MySQL Query not working with PHP?

[fishbaitfood]

Hi there,

 

I have this query, which outputs the records properly in PhpMyAdmin.

But when using this query inside my PHP code, the result is incomplete.

Instead of retrieving the expected 15 results, I only get the first one twice.

Also, I don't get any errors.

 

<?php
$get_files = "SELECT `id` FROM `files` WHERE `category` = 'W';";

include_once('connection.inc.php');
$con = mysql_connect(MYSQL_SERVER, MYSQL_USERNAME, MYSQL_PASSWORD) or die ("Connection failed: " . mysql_error());
$dbname = "myDB";
$select_db = mysql_select_db($dbname, $con) or die ("Can't open database: " . mysql_error());

$result_files = mysql_query($get_files) or die ("Query failed: " . mysql_error());
$files = mysql_fetch_array($result_files);
$W_files = "'".implode("', '", $files)."'";

// echo $W_files;	outputs the first number twice, instead of the expected 15 different ones

$get_checklist = "SELECT * FROM `checklist` WHERE `id` IN ($W_files) ORDER BY `id` ASC;";
$result_checklist = mysql_query($get_checklist) or die ("Query failed: " . mysql_error());

// .... code for displaying
?>

[ManiacDan]

You need to use loops to get all the rows.  Right now you're just fetching the first result of the first query, and using it as the only ID in your second query.

 

The manual has proper syntax.

[fishbaitfood]

ManiacDan, ofcourse!

Totally forgot about it, because I already have a loop for displaying the results.

 

Sorry for starting this topic!

Thanks again.

[ManiacDan]

You should actually be doing this with one JOIN to join these two tables.

[fishbaitfood]

Hmm, yeah, I know, but I'm having trouble linking my two tables in MySQL.

It's causing me a lot of errors.

[ManiacDan]

SELECT checklist.* from checklist JOIN files ON checklist.id = files.id WHERE files.category = 'W'

[fishbaitfood]

Oh yeah, linking my tables with foreign keys isn't necessary for this.

I need to dig in some more where using foreign keys is handy.

 

Thanks for the JOIN query! Saved me severel rows of code!

[ManiacDan]

More importantly, it saved you a round-trip to the database.  500 lines of PHP can execute in the time it takes to make a round-trip to the database to execute a query.

[fishbaitfood]

Indeed.. Thanks a bunch ManiacDan!