Asked  2 Years ago    Answers:  5   Viewed   65 times

I'm creating an installation script for an application that I'm developing and need to create databases dynamically from within PHP. I've got it to create the database but now I need to load in several .sql files. I had planned to open the file and mysql_query it a line at a time - until I looked at the schema files and realised they aren't just one query per line.

So, how do I load an sql file from within PHP (as phpMyAdmin does with its import command)?

 Answers

1

I'm getting the feeling that everyone here who's answered this question doesn't know what it's like to be a web application developer who allows people to install the application on their own servers. Shared hosting, especially, doesn't allow you to use SQL like the "LOAD DATA" query mentioned previously. Most shared hosts also don't allow you to use shell_exec.

Now, to answer the OP, your best bet is to just build out a PHP file that contains your queries in a variable and can just run them. If you're determined to parse .sql files, you should look into phpMyAdmin and get some ideas for getting data out of .sql files that way. Look around at other web applications that have installers and you'll see that, rather than use .sql files for their queries, they just package them up in PHP files and just run each string through mysql_query or whatever it is that they need to do.

Wednesday, December 21, 2022
4

For some reason, changing the first line of the query:

SELECT SQL_CALC_FOUND_ROWS p.*

To:

SELECT SQL_CALC_FOUND_ROWS p.id

And then looping through the results getting the data I needed from each row in separate queries got the result I was after.

Friday, December 23, 2022
 
4

A few things to consider:

  • SQLite3 DB files grow roughly in multiples of the DB page size and do not shrink unless you use VACUUM. If you delete some rows, the freed space is marked internally and reused in later inserts. Therefore an insert will often not cause a change in the size of the backing DB file.

  • You should not use traditional backup tools for SQLite (or any other database, for that matter), since they do not take into account the DB state information that is critical to ensure an uncorrupted database. Especially, copying the DB files in the middle of an insert transaction is a recipe for disaster...

  • SQLite3 has an API specifically for backing-up or copying databases that are in use.

  • And yes, it does seem that your DB files are corrupted. It could be a hardware/filesystem error. Or perhaps you copied them while they were in use? Or maybe restored a backup that was not properly taken?

Friday, September 23, 2022
 
ohadr
 
1

mysqli_stmt::bind_param can only bind a specific variable, not an expression. The supplied variable is passed to 'bind' by reference, and not by value, meaning that the underlying SQL gets whatever value that variable has at the time the command is executed, not when it's bound.

Use:

WHERE field LIKE CONCAT('%', ?, '%")

or do:

$brand = '%' . $brand . '%'

immediately before the command is executed.

What you can't do is:

WHERE field LIKE '%?%

because the ? bound variable must correspond to a single string or numeric value, not to a substring (or field name).

EDIT in this case, your real problem appears to be mixing up prepared statements (as supported by mysqli::prepare and mysqli_stmt::execute()) with plain old queries (as done with mysqli::query()). You should also just ask for the number of rows directly from the DB server, rather than pull the data and use num_rows:

$countQuery = "SELECT COUNT(ARTICLE_NO) FROM AUCTIONS WHERE upper(ARTICLE_NAME) LIKE ?";
if ($numRecords = $con->prepare($countQuery)) {
    $numRecords->bind_param("s",  $brand);
    $numRecords->execute();
    $numRecords->bind_result($num_rows);
    $numRecords->fetch();
    $numRecords->free_result();
    $numRecords->close();
    $last = ceil($rowcount/$page_rows);
} else {
    print_r($con->error);
}
Thursday, October 13, 2022
 
2

Assuming that t_node is node Table instance, while t_node_node - node_node Table instance, see the statement below.

upd = (t_node.update()
        .values(
            parent_id = t_node_node.c.parent_id,
            label = t_node_node.c.label,
            )
        .where(t_node_node.c.child_id == t_node.c.node_id)
        )

Read more on Inserts, Updates and Deletes documentation for more information.

Saturday, August 6, 2022
 
Only authorized users can answer the search term. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 

Browse Other Code Languages