By Naim


2012-05-16 00:44:49 8 Comments

<?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.

3 comments

@Naim 2012-05-19 10:16:06

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));
}

@Paul Dessert 2012-05-16 00:56:33

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'];
}

?>

@Okonomiyaki3000 2012-05-16 01:01:51

Don't forget your semicolons.

@Paul Dessert 2012-05-16 01:04:14

@ElijahMadden - Where did I forget a ; ?

@Okonomiyaki3000 2012-05-16 01:10:24

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

@Naim 2012-05-16 01:23:05

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

@Naim 2012-05-16 17:58:34

@ElijahMadden could you please tell me where I should put those semi colons ?

@Naim 2012-05-16 22:20:32

@Paul - Thanks Paul but I haven't noticed anything different after the edit, it looks like the exact same code as yesterday to me..am I missing something???

@Paul Dessert 2012-05-16 22:23:31

@Naim - I added spaces after some of the " i.e. " CREATE VIEW Now has a space in front of it.

@Naim 2012-05-17 10:19:08

@Paul, it worked even though I got another error saying I can't use create view on sub-queries or local variables, but that's out of the scope on this question.

@Okonomiyaki3000 2012-05-18 00:25:20

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.

@Naim 2012-05-18 22:47:13

@ElijahMadden - Thanks and I have just read about it on php.net. Also I am trying now to use mysqli_multi_query to run multiple queries but now am getting other type of errors..still digging...

@Okonomiyaki3000 2012-05-16 00:49:20

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)

@Naim 2012-05-16 00:52:31

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

@Okonomiyaki3000 2012-05-16 01:03:25

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 2012-05-16 01:12:18

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 2012-05-18 00:22:08

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 2012-05-18 22:49:19

@ 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.

@Okonomiyaki3000 2012-05-21 00:32:13

Yeah, let me have a look at some of those. Maybe some of them can be simplified.

Related Questions

Sponsored Content

30 Answered Questions

[SOLVED] How do I get PHP errors to display?

30 Answered Questions

[SOLVED] How do you parse and process HTML/XML in PHP?

9 Answered Questions

[SOLVED] How do I break a string over multiple lines?

18 Answered Questions

[SOLVED] Reference — What does this symbol mean in PHP?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

37 Answered Questions

[SOLVED] Deleting an element from an array in PHP

  • 2008-12-15 20:28:55
  • Ben
  • 2502299 View
  • 2407 Score
  • 37 Answer
  • Tags:   php arrays unset

7 Answered Questions

[SOLVED] How does PHP 'foreach' actually work?

15 Answered Questions

[SOLVED] Why shouldn't I use mysql_* functions in PHP?

  • 2012-10-12 13:18:39
  • Madara's Ghost
  • 214989 View
  • 2459 Score
  • 15 Answer
  • Tags:   php mysql database

31 Answered Questions

[SOLVED] startsWith() and endsWith() functions in PHP

  • 2009-05-07 12:14:27
  • Click Upvote
  • 814858 View
  • 1434 Score
  • 31 Answer
  • Tags:   php string

Sponsored Content