bschultz Posted August 22, 2009 Share Posted August 22, 2009 I need to add an "if no matches" section to this code. <?php mysql_select_db('2009fb',$dbc); $sql = " SELECT * FROM (select number as lt1_number, name as lt1_name, pos as lt1_pos, height as lt1_height, weight as lt1_weight, year as lt1_year, city as lt1_city, state as lt1_state FROM $db_select_own WHERE number = $lt1) as lt1, (select number as lt2_number, name as lt2_name, pos as lt2_pos, height as lt2_height, weight as lt2_weight, year as lt2_year, city as lt2_city, state as lt2_state FROM $db_select_own WHERE number = $lt2) as lt2"; $rs = mysql_query($sql,$dbc); if (mysql_num_rows($rs) > 0){ while ($row = mysql_fetch_assoc($rs)) { echo "<table border='0'><tr><div class='position'>$left_tackle</div></tr><tr>"; echo "<td class='number'>$row[lt1_number]</td><td><div class='name'>$row[lt1_name]</div><div class='height'>$row[lt1_height], $row[lt1_weight] - $row[lt1_year]<br />$row[lt1_city] $row[lt1_state] <br /><br /></div></td></tr><tr>"; echo "<td class='number'>$row[lt2_number]</td><td><div class='name'>$row[lt2_name]</div><div class='height'>$row[lt2_height], $row[lt2_weight] - $row[lt2_year]<br />$row[lt2_city] $row[lt2_state] <br /><br /></div></td></tr></table>"; }}else{ echo (""); } ?> The lt1 part will ALWAYS have a match. The lt2 part may, or may not, have a match. If there isn't a match, I would need to display a blank table. I have no idea how, or where, to add this part...for just the lt2 select. if (! $matches) { echo ("<tr><td></td><td>"); } Quote Link to comment Share on other sites More sharing options...
kickstart Posted August 22, 2009 Share Posted August 22, 2009 Hi Short solution:- <?php mysql_select_db('2009fb',$dbc); $sql = " SELECT * FROM (select number as lt1_number, name as lt1_name, pos as lt1_pos, height as lt1_height, weight as lt1_weight, year as lt1_year, city as lt1_city, state as lt1_state FROM $db_select_own WHERE number = $lt1) as lt1, (select number as lt2_number, name as lt2_name, pos as lt2_pos, height as lt2_height, weight as lt2_weight, year as lt2_year, city as lt2_city, state as lt2_state FROM $db_select_own WHERE number = $lt2) as lt2"; $rs = mysql_query($sql,$dbc); if (mysql_num_rows($rs) > 0) { while ($row = mysql_fetch_assoc($rs)) { echo "<table border='0'><tr><div class='position'>$left_tackle</div></tr><tr>"; echo "<td class='number'>$row[lt1_number]</td><td><div class='name'>$row[lt1_name]</div><div class='height'>$row[lt1_height], $row[lt1_weight] - $row[lt1_year]<br />$row[lt1_city] $row[lt1_state] <br /><br /></div></td></tr><tr>"; echo "<td class='number'>$row[lt2_number]</td><td><div class='name'>$row[lt2_name]</div><div class='height'>$row[lt2_height], $row[lt2_weight] - $row[lt2_year]<br />$row[lt2_city] $row[lt2_state] <br /><br /></div></td></tr></table>"; } } echo ("<table border='0'><tr><td></td><td></tr></table>"); } ?> However I am a bit uncertain of the code. Will there only ever be 1 record returned by the first subselect and 0 or 1 from the second? As with your current code if (say) both returned 2 rows the there would be a total of 4 combinations output. Also if there are more than a single row returned then you will put out a seperate table for each row, and if only a single row is output you might as well use if ($row = mysql_fetch_assoc($rs)) instead of while ($row = mysql_fetch_assoc($rs)). All the best Keith Quote Link to comment Share on other sites More sharing options...
bschultz Posted August 22, 2009 Author Share Posted August 22, 2009 If there is a matching record, there will only be one of them. lt1 will ALWAYS have only 1 match...lt2 will have 0 or 1 match...but never more than 1. I'll give your code a try....thanks! Quote Link to comment Share on other sites More sharing options...
bschultz Posted August 22, 2009 Author Share Posted August 22, 2009 no luck...same error as before Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource Quote Link to comment Share on other sites More sharing options...
kickstart Posted August 22, 2009 Share Posted August 22, 2009 Hi That suggests you have an error in your SQL. What is the value of $db_select_own? Change the query line to:- $rs = mysql_query($sql,$dbc) or die(mysql_error()); and see what the error message is. All the best Keith Quote Link to comment Share on other sites More sharing options...
bschultz Posted August 22, 2009 Author Share Posted August 22, 2009 $db_select_own is defined earlier in the code...it's the name of the table and that works. I added the or die...and it died You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') as lt2' at line 3 Quote Link to comment Share on other sites More sharing options...
kickstart Posted August 22, 2009 Share Posted August 22, 2009 Hi Nothing obvious (assuming that the column number is a numeric field and not a character field). Can you echo out the SQL:- $rs = mysql_query($sql,$dbc) or die("$sql - ".mysql_error()); All the best Keith Quote Link to comment Share on other sites More sharing options...
bschultz Posted August 22, 2009 Author Share Posted August 22, 2009 echo of $rs is this: Resource id #5 Quote Link to comment Share on other sites More sharing options...
kickstart Posted August 22, 2009 Share Posted August 22, 2009 Hi Not $rs, echo out $sql. All the best Keith Quote Link to comment Share on other sites More sharing options...
bschultz Posted August 22, 2009 Author Share Posted August 22, 2009 SELECT * FROM (select number as lt1_number, name as lt1_name, pos as lt1_pos, height as lt1_height, weight as lt1_weight, year as lt1_year, city as lt1_city, state as lt1_state FROM nick_own WHERE number = 54) as lt1, (select number as lt2_number, name as lt2_name, pos as lt2_pos, height as lt2_height, weight as lt2_weight, year as lt2_year, city as lt2_city, state as lt2_state FROM nick_own WHERE number = 100) as lt2 This is correct. There is no #100 in the DB, so it should kick in the "no matches" Quote Link to comment Share on other sites More sharing options...
kickstart Posted August 23, 2009 Share Posted August 23, 2009 Hi If there is an error in the SQL (which is what it seems to be complaining about) then whether there are matching records or not is irrelevant as the SQL will never be executed. I knocked up a dummy table and ran that SQL against it and it worked fine, so I am a bit stumped as to what the problem is. If you copy that SQL into phpmyadmin (or equivalent) and try and execute it what do you get? All the best Keith Quote Link to comment Share on other sites More sharing options...
bschultz Posted August 23, 2009 Author Share Posted August 23, 2009 phpmyadmin ran the query just fine Quote Link to comment Share on other sites More sharing options...
bschultz Posted August 23, 2009 Author Share Posted August 23, 2009 Kickstart...thank you for the help! I need to get this working today, so I'm just going to separate it out, and run two selects and two queries. Thanks again! Brian Quote Link to comment Share on other sites More sharing options...
kickstart Posted August 23, 2009 Share Posted August 23, 2009 Hi No problems. Sorry I couldn't find the real problem for you, but at least it has hopefully pointed you in the right direction. All the best Keith Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.