php/mysql with multiple queries

Question

<?php

$query1 = "CREATE VIEW current_rankings AS SELECT * FROM main_table WHERE date = X";

$query2 = "CREATE VIEW previous_rankings AS SELECT rank FROM main_table WHERE date = date_sub('X', INTERVAL 1 MONTH)";

$query3 = "CREATE VIEW final_output AS SELECT current_rankings.player, current_rankings.rank as current_rank LEFT JOIN previous_rankings.rank as prev_rank
             ON (current_rankings.player = previous_rankings.player)";

$query4 = "SELECT *, @rank_change = prev_rank - current_rank as rank_change from final_output";

$result = mysql_query($query4) or die(mysql_error()); 

while($row = mysql_fetch_array($result)) {
echo $row['player']. $row['current_rank']. $row['prev_rank']. $row['rank_change'];
}

?>

All the queries work independently but am really struggling putting all the pieces together in one single result so I can use it with mysql_fetch_array.

I've tried to create views as well as temporary tables but each time it either says table does not exist or return an empty fetch array loop...logic is there but syntax is messed up I think as it's the 1st time I had to deal with multiple queries I need to merge all together. Looking forward to some support. Many thanks.

i'm quite certain there's a much more efficient way of doing this, but without details about those tables, it's a little hard to say. — GDP
– GDP, Commented May 16, 2012 at 0:52
Actual queries are much longer, I've tried to ask a compact question for a better overall picture. Nonetheless, the structure is there and further details are not relevant as it comes at the end to merge all 4 queries or execute them one after the other. — Naim
– Naim, Commented May 16, 2012 at 0:56
Why do you need to keep creating the views? Doesn't the view basically save your query and runs it again when you select from it? — Gohn67
– Gohn67, Commented May 16, 2012 at 1:28
exactly and this is why I use them. The results are cascading down from the parent views. — Naim
– Naim, Commented May 16, 2012 at 1:31

Naim · Accepted Answer · 2021-03-02 11:28:09Z

23

Thanks to php.net I've come up with a solution : you have to use (mysqli_multi_query($link, $query)) to run multiple concatenated queries.

 /* create sql connection*/
$link = mysqli_connect("server", "user", "password", "database");

$query = "SQL STATEMENTS;"; /*  first query : Notice the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS;"; /* Notice the dot before = and the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS;"; /* Notice the dot before = and the 2 semicolons at the end ! */
$query .= "SQL STATEMENTS"; /* last query : Notice the dot before = at the end ! */

/* Execute queries */

if (mysqli_multi_query($link, $query)) {
do {
    /* store first result set */
    if ($result = mysqli_store_result($link)) {
        while ($row = mysqli_fetch_array($result)) 

/* print your results */    
{
echo $row['column1'];
echo $row['column2'];
}
mysqli_free_result($result);
}   
} while (mysqli_next_result($link));
}

EDIT - The solution above works if you really want to do one big query but it's also possible to execute as many queries as you wish and execute them separately.

$query1 = "Create temporary table A select c1 from t1"; 
$result1 = mysqli_query($link, $query1) or die(mysqli_error());

$query2 = "select c1 from A"; 
$result2 = mysqli_query($link, $query2) or die(mysqli_error());

while($row = mysqli_fetch_array($result2)) {

echo $row['c1'];
    }

edited Mar 2, 2021 at 11:28

answered May 19, 2012 at 10:16

Naim

4631 gold badge4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dharman Over a year ago

Don't use mysqli_multi_query(). You should execute all these queries separately.

Xonshiz Over a year ago

Explanation of why mysqli_multi_query() should be avoided.

Okonomiyaki3000 · Accepted Answer · 2012-05-16 01:01:06Z

0

It seems you are not executing $query1 - $query3. You have just skipped to $query4 which won't work if the others have not been executed first.

Also

$query4 = "SELECT *, @rank_change = prev_rank - current_rank as rank_change from final_output";

should probably be

$query4 = "SELECT *, @rank_change := prev_rank - current_rank as rank_change from final_output";

or else the value of rank_change will just be a boolean, true if @rank_change is equal to (prev_rank - current_rank), false if it is not. But do you need @rank_change at all? Will you use it in a subsequent query? Maybe you can remove it altogether.

Even better, you could just combine all the queries into one like this:

SELECT 
    curr.player,
    curr.rank AS current_rank,
    @rank_change := prev.rank - curr.rank AS rank_change
FROM
    main_table AS curr
    LEFT JOIN main_table AS prev
        ON curr.player = prev.player    
WHERE 
    curr.date = X
    AND prev.date = date_sub('X', INTERVAL 1 MONTH)

edited May 16, 2012 at 1:01

answered May 16, 2012 at 0:49

Okonomiyaki3000

3,70625 silver badges23 bronze badges

6 Comments

Naim Over a year ago

True, but this is exactly what I am trying to figure out : how to execute all 4 queries.

Okonomiyaki3000 Over a year ago

You can just run them one after another with multiple calls to mysql_query() or concatenate them as suggested by Paul, or rewrite them as one query (in my edit above).

Naim Over a year ago

Thanks Elijah, but running one single query is impossible in my case, as I need very complex queries from main_table, so I have to go step by step using sub-queries. Will try Paul's suggestion and revert back to him.

Okonomiyaki3000 Over a year ago

I don't know about other queries you may be running on this table but, in this instance, you can certainly do it with one relatively simple query.

Naim Over a year ago

@ Elijah - That would be ideal, still am not sure it would be possible as every query contains multiple sub-queries and use different user variables...will post as a new question and post the link here for you. Thanks.

|

Paul Dessert · Accepted Answer · 2012-05-16 21:21:12Z

-1

You should concatenate them:

<?php

$query = "CREATE VIEW current_rankings AS SELECT * FROM main_table WHERE date = X";

$query .= " CREATE VIEW previous_rankings AS SELECT rank FROM main_table WHERE date =     date_sub('X', INTERVAL 1 MONTH)";

$query .= " CREATE VIEW final_output AS SELECT current_rankings.player,     current_rankings.rank as current_rank LEFT JOIN previous_rankings.rank as prev_rank
         ON (current_rankings.player = previous_rankings.player)";

$query .= " SELECT *, @rank_change = prev_rank - current_rank as rank_change from final_output";

$result = mysql_query($query) or die(mysql_error()); 

while($row = mysql_fetch_array($result)) {
echo $row['player']. $row['current_rank']. $row['prev_rank']. $row['rank_change'];
}

?>

edited May 16, 2012 at 21:21

answered May 16, 2012 at 0:56

Paul Dessert

6,3879 gold badges50 silver badges74 bronze badges

10 Comments

Okonomiyaki3000 Over a year ago

Don't forget your semicolons.

Paul Dessert Over a year ago

@ElijahMadden - Where did I forget a ; ?

Okonomiyaki3000 Over a year ago

Your queries will need them if you concatenate into a single string.

Naim Over a year ago

Hi Paul I've added this dot before = as showed in your answer and it returned a SQL syntax error...

Okonomiyaki3000 Over a year ago

I don't believe that separating these queries by spaces is correct. As each is meant to be a separate query, they should be separated by semicolons. Ex: $query = "CREATE VIEW current_rankings AS SELECT * FROM main_table WHERE date = X;"; (notice the semicolon after X, in the query.

|

Collectives™ on Stack Overflow

php/mysql with multiple queries

3 Answers 3

2 Comments

6 Comments

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

6 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related