-1

I am relatively new to web development and I am trying to query a MySQL database with a database through the following commands, however I unable to do so and I am getting the following as error:

PHP Code:

$query1 = "SELECT id_2 FROM idTable WHERE id = '$idno'";
    $result1 = mysql_query($query1);
    if (!$result1)
        die("Database access failed(error 7): " . mysql_error());

    /************ possible error point *******************/
    $query2 = "SELECT * FROM Data NATURAL JOIN $result1 LIMIT $num,$last_num";
    $result = mysql_query($query2);
    if (!$result)
        die("Database access failed(error 8): " . mysql_error());

Error:

Database access failed(error 8): Table 'Database.Resource' doesn't exist

Basically I have two tables. I need to choose some values from the 'id_2' column of idTable and depending upon the values chosen, I want to pick all the rows from the table 'Data' that match the corresponding ids by performing a join operation. Can anybody please tell me how to achieve the join of the resource returned and the table (Or in general, how do I solve my problem)?

Python_user
  • 1,378
  • 3
  • 12
  • 25
  • check your connection settings mysql_connect('localhost', 'mysql_user', 'mysql_password'); – asfandahmed1 May 13 '15 at 16:31
  • @asfandahmed1You mean the connection of the php to the database? My php script is able to connect to the database without any problem since it is able to execute the first query... – Python_user May 13 '15 at 16:33
  • this will tell if you are able to connect. $link = mysql_connect('localhost', 'mysql_user', 'mysql_password'); if($link) echo 'success' else die('could not connect') – asfandahmed1 May 13 '15 at 16:34
  • @asfandahmed1I already have it in the beginning of my php code... Sorry, I forgot to mention that and I also have the 'die' function in case it is not able to connect... And it succesfully passes it... – Python_user May 13 '15 at 16:38
  • 1
    you can not do `"SELECT * FROM Data NATURAL JOIN $result1 LIMIT $num,$last_num";` you have to write the query like `"SELECT * FROM Data NATURAL JOIN idtable ON ... LIMIT $num,$last_num";` – Alex May 13 '15 at 16:39
  • `$result1` is phpvar with mysql resource! it is not a string @Strawberry – Alex May 13 '15 at 16:41
  • @Strawberry Basically, I want to select some rows from a column (i.e., a single column that contains some of the ids) and I want to join it another table that contains the data with all of the selected ids... How do I do that? – Python_user May 13 '15 at 16:42
  • @Alex How do I do the function in the comment above? – Python_user May 13 '15 at 16:42
  • @Strawberry They are in the 'id' column in the idTable... – Python_user May 13 '15 at 16:43
  • 2
    Please, [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php). They are no longer maintained and are [officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). Learn about [prepared statements](http://en.wikipedia.org/wiki/Prepared_statement) instead, and consider using PDO, [it's not as hard as you think](http://jayblanchard.net/demystifying_php_pdo.html). – Jay Blanchard May 13 '15 at 16:45
  • Forget about the PHP for the time being. Instead, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. – Strawberry May 13 '15 at 16:46
  • @Strawberry I am extremely sorry.. Rectified the code.... – Python_user May 13 '15 at 16:46

2 Answers2

1
SELECT d.* 
  FROM Data d
  JOIN idtable i
    ON i.id2 = d.id
 WHERE i.id = $idno
 ORDER
    BY d.id
 LIMIT $num,$last_num;
Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • Thanks! This query worked... And I dont have to issue 'query1' separately and fall into the trouble of trying to merge the resource and the table... – Python_user May 13 '15 at 17:02
0

I am still not sure what is relations between these 2 tables but valid code and query should be something like :

$query1 = "SELECT * FROM Data 
   INNER JOIN idtable 
   ON idtable.id = Data.id_2
      AND idtable.id = $idno
   LIMIT $num,$last_num";
$result = mysql_query($query1);
if (!$result)
    die("Database access failed : " . mysql_error());

But valid is not equal correct or good :-)

And once I've reread my own query, I have the same question as @Strawberry: why do we need join? why not just:

$query1 = "SELECT * FROM Data 
   WHERE Data.id_2 = $idno
   LIMIT $num,$last_num";
$result = mysql_query($query1);
if (!$result)
    die("Database access failed : " . mysql_error());
Alex
  • 16,739
  • 1
  • 28
  • 51