CODEWITHSUNDEEP
phpmysql
Conner Burnett
Conner Burnett

Reputation: 512

Running more than one Mysql Query using PHP

I am needing to run more than one Mysql Query using PHP. I have a site and pull all the information from the databse

$sql = "SELECT * FROM $table WHERE ID=$escape";
$query = mysql_query($sql) or die(mysql_error());
$rentals = mysql_fetch_assoc($query);

Now I have two other queries I need to also run for Previous and Next Buttons

$sqlPrev = 'SELECT `id` FROM `table`
        WHERE `id` < '$curId' AND `catId` = '$curCat'
        ORDER BY `id` DESC LIMIT 1;

$sqlNext = 'SELECT `id` FROM `table`
        WHERE `id` > '$curId' AND `catId` = '$curCat'
        ORDER BY `id` ASC LIMIT 1;

I have the coding right when I run these in PHP MyAdmin, however when I try to execute them via the website I get a mysql error!

Upvotes: 1

Views: 2752

Answers (4)

rauprog
rauprog

Reputation: 243

For more than one query, you need to use multi query or close the result before calling another query. For example if you write in the object oriented style.

$conn = new mysqli($servername, $username, $password, $dbname);
$sql="SELECT * FROM mytable";
$result=$conn->query($sql);
echo $result->num_rows;
$sql="SELECT id FROM mytable";
$result2=$conn->query($sql);
echo $result2->num_rows; // does not work because result was not closed.

To get it to work, write this instead.

$conn = new mysqli($servername, $username, $password, $dbname);
$sql="SELECT * FROM mytable";
$result=$conn->query($sql);
echo $result->num_rows;
$result->close();  //*********notice this new line. result 
//needs to be closed before calling another query
$sql="SELECT id FROM mytable";
$result2=$conn->query($sql);
echo $result2->num_rows; // **this does work because previous result 
// was closed.

If you need to do multiple queries at once or need to do a query before you finish outputting rows for the previous one, use multi query. I use multi query for the second reason, getting another query before outputting all the rows of the previous one. This is important for nested queries, such as one in the middle of another, such as in multiple nested threads in a forum, or for nested replies used on some web pages.

Here is an example of one query done in the middle of another query.

$conn = new mysqli($servername, $username, $password, $dbname);
$sql="SELECT * FROM mytable";
$conn->multi_query($sql);
$result=$conn->store_result();
$count=0;
while($row = $result->fetch_assoc()) {
   echo "$result['id'] $result['name'];
   if ($count==0) {
   // now you can do another query in the middle of this one
   $sql="SELECT id FROM mytable";
   $conn->multi_query($sql);
   $result2=$conn->store_result();
   $row2=$result2->fetch_assoc();
   echo "result of second query is: $row2['id'] $row2['name']";
   }
   $count=$count+1;
}

Upvotes: 0

drew010
drew010

Reputation: 69977

mysql_query can only execute one query at a time.

Basically you just need to have 3 calls to mysql_query.

$sql = "SELECT * FROM $table WHERE ID=$escape";
$query = mysql_query($sql) or die(mysql_error());
$rentals = mysql_fetch_assoc($query);

$sqlPrev = 'SELECT `id` FROM `table`
        WHERE `id` < ' . $curId . ' AND `catId` = ' . $curCat . '
        ORDER BY `id` DESC LIMIT 1';

$sqlNext = 'SELECT `id` FROM `table`
        WHERE `id` > ' . $curId . ' AND `catId` = ' . $curCat . '
        ORDER BY `id` ASC LIMIT 1';

$resultPrev = mysql_query($sqlPrev);
$resultNext = mysql_query($sqlNext);

// todo: check that the above queries executed successfully
// if (!$resultPrev) echo mysql_error();

if (mysql_num_rows($resultPrev)) {
    $prev = mysql_fetch_array($resultPrev);
    $prevId = $prev['id'];
} else {
    $prevId = null; // there is no previous item
}

if (mysql_num_rows($resultNext)) {
    $next = mysql_fetch_array($resultNext);
    $nextId = $next['id'];
} else {
    $nextId = null; // there is no next item
}

Upvotes: 0

spencer7593
spencer7593

Reputation: 108500

You probably need to add the concatenation operator (.) between the string literals and the variables. (It's required in Perl; I do the same thing in PHP.)

$sqlPrev = 'SELECT `id` FROM `table`
    WHERE `id` < '.$curId.' AND `catId` = '.$curCat.'
    ORDER BY `id` DESC LIMIT 1';

Echo the SQL text that is being sent to the database. That will reveal the problem.

Upvotes: 0

androbin
androbin

Reputation: 1759

Problems are the " instead of ' at $sqlPrev and $sqlNext. {$curID} only works with "". And there's no end " or '.

Upvotes: 1

Related Questions