Asked  8 Months ago    Answers:  5   Viewed   37 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

63

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, March 31, 2021
 
Strae
answered 8 Months ago
94

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.

Saturday, May 29, 2021
 
linjuming
answered 5 Months ago
74

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);
}
Saturday, July 31, 2021
 
Abdul Rehman
answered 3 Months ago
91

Didn't see any update about FILESTREAM support in EF. (Mentioned before as partial support with .net 3.5 sp1 release here). I suppose entity framework is accessing FILESTREAM through TSQL and apparent that you will not be able to get the streaming performance benefits of FILESTREAM. (need to read all file content into memory)

So, the recommended approach is using with SqlFileStream .Net API.

http://lennilobel.wordpress.com/2011/08/22/using-sqlfilestream-in-c-to-access-sql-server-filestream-data/

Tuesday, August 10, 2021
 
user2671488
answered 3 Months ago
82

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.

Thursday, August 12, 2021
 
jakubos
answered 3 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :