PHP and SQL - Consult and WHERE clause with variable

Question

I'm actually coding the schedule of some conferences and I'm a little bit derping with the php code.

The code is the following:

<?php
mysql_connect("localhost","root","root");
mysql_select_db("pbeusr3");

$sql="SELECT id_s, sessionname FROM sessions";


$result=mysql_query($sql);
$i=0;
if($result!=NULL)
{
    if(mysql_num_rows($result)>0)
    {
        while($row=mysql_fetch_array($result))
        {
           //here you can work with the results...
             echo "Session ".$row['id_s'].":" .$row['sessionname'];
             echo "<br>";
             $a = $row['id_s'];

             $sql2="SELECT id_p FROM presentations WHERE id_s='$a'";
             $result2 = mysql_query($sql2);
             $sql3="SELECT title, author1 FROM papers WHERE id_p='$result2'";
             $result3 = mysql_query($sql3);

             while($row2=  mysql_fetch_array($result3))
             {
                 echo "Title ".$row2['title']. " Author: " .$row2['author1'];
                 echo "<br>";
             }

         }
    }
    else
    {
    }
    mysql_free_result($result);
}

mysql_close();
?>

The output of this code is intended to be something like Session: Name of the session. Title of the paper and author of that session.

Instead I only see the Session and Name of the session (first fetch) but then I can't see the paper part (second fetch).

Any help?

Thank you

Look at where $result2 comes from, and where you're using it - it won't be the right type of data to substitute straight into the next query like that. — IMSoP, Apr 13 '14 at 11:07
Also, have you looked into moving to MySQLi or PDO yet? The old mysql functions you're using here are no longer maintained, and will be removed in a future release of PHP. — IMSoP, Apr 13 '14 at 11:09

score 0 · Accepted Answer · edited May 25 '19 at 13:54

This bit:

     $sql2="SELECT id_p FROM presentations WHERE id_s='$a'";
     $result2 = mysql_query($sql2);
     $sql3="SELECT title, author1 FROM papers WHERE id_p='$result2'"; #<-- here

Is incorrect, you are trying to use the result set directly into the query. Instead, you need to retrieve that row and use that in your query like:

     $sql2="SELECT id_p FROM presentations WHERE id_s='$a'";
     $result2 = mysql_query($sql2);
     //fetch the row from the result set
     $row_id_p = mysql_fetch_assoc($result2);
     $sql3="SELECT title, author1 FROM papers WHERE id_p='".$row_id_p['id_p']."'";

Note:

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

score 0 · Answer 2 · answered Apr 15 '14 at 05:13

Try this one

$sql="SELECT id_s, sessionname FROM sessions";
$result=mysql_query($sql);
$i=0;

    if(!empty($result))
    {
        while($row=mysql_fetch_array($result))
        {
           //here you can work with the results...
             echo "Session ".$row['id_s'].":" .$row['sessionname'];
             echo "<br>";
             $a = $row['id_s'];

             $sql2="SELECT id_p FROM presentations WHERE id_s='$a'";
             $result2 = mysql_query($sql2);
             if(!empty($result2))
             { 
                 $rowselect=  mysql_fetch_array($result2)
                 $b = $rowselect['id_p']; 
             }else $b='';
             $sql3="SELECT title, author1 FROM papers WHERE id_p='$b' ";
             $result3 = mysql_query($sql3);

             if(!empty($result3))
             {
                 while($row2=  mysql_fetch_array($result3))
                 {
                     echo "Title ".$row2['title']. " Author: " .$row2['author1'];
                     echo "<br>";
                 }
             }
             else
             {
                  //else statement if needed
             } 
             echo "<br>";

         }
    }
    else
    {
         //else statement
    }
  mysql_free_result($result);


mysql_close();
?>

PHP and SQL - Consult and WHERE clause with variable

2 Answers2

Note: