Sarajs Posted January 24, 2009 Share Posted January 24, 2009 Hello people!! I'm new to PHP and this forum I was hoping you can help, I have a uni project on and can't figure out where I've gone wrong . Getting this error: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/.koshi/saras2740/saras.ccacolchester.com/webprog/SoundsGood/webdevstie/search.php on line 115 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html><!-- InstanceBegin template="/Templates/maintemp.dwt.php" codeOutsideHTMLIsLocked="false" --> <head> <!-- InstanceBeginEditable name="doctitle" --> <?php $title = "" ?> <?php $cd_artist = ''; $cd_title = ''; $cd_genre = ''; $cd_year = ''; $cd_type = 1; $cd_productID = 0; if (empty($_POST['artist'])) {$cd_artist = '';} else {$cd_artist = $_POST['artist'];} if (empty($_POST['title'])) {$cd_title = '';} else {$cd_title = $_POST['title'];} if (empty($_POST['genre'])) {$cd_genre = '';} else {$cd_genre = $_POST['genre'];} if (empty($_POST['year'])) {$cd_year = '';} else {$cd_year = $_POST['year'];} if (empty($_POST['type'])) {$cd_type = '';} else {$cd_type = $_POST['type'];} ?> <!-- InstanceEndEditable --> <?php include('includes/head.inc'); ?> <!-- InstanceBeginEditable name="head" --> <!-- InstanceEndEditable --> </head> <body> <div class="container"> <?php include('includes/header.inc'); ?> <!-- InstanceBeginEditable name="content" --> <div class="marginbottom"> <div class="left530"><h1>Search Products</h1></div> <div class="clearboth"></div> <div class="formcontainter"> <form action="search.php" method="post"> <table> <tr> <td class="coltype1"> Artist </td> <td class="coltype2"> <input id="artist" type="text" name="artist" /> </td> <td class="coltype3"> </td> <td class="coltype1"> Title </td> <td class="coltype2"> <input id="title" type="text" name="title" /> </td> </tr> <tr> <td class="coltype1"> Genre </td> <td class="coltype2"> <input id="genre" type="text" name="genre" /> </td> <td class="coltype3"> </td> <td class="coltype1"> Year </td> <td class="coltype2"> <input id="year" type="text" name="year" /> </td> </tr> <tr> <td class="coltype1"> Type </td> <td class="coltype2"> <select id="type" name="type"> <option value="1">CD</option> <option value="2">DVD</option> </select> </td> <td class="coltype3" colspan="2"> </td> <td class="coltype2"> <input class="submit" id="search products" type="submit" value="Search" /> </td> </tr> </table></form> </div> <div class="clearboth"></div> <div class="results"> <h2>Search Results</h2> <table> <?php require('includes/DBConnection.inc'); $result = mysql_query("SELECT * FROM tProducts WHERE artist='$cd_artist', title='$cd_title', genre='$cd_genre', year='$cd_year', type='$cd_type'"); while($row = mysql_fetch_array($result)) { echo "<tr><td class=\"icon\"><img src=\"\" /></td>"; echo "<td><span class=\"orange\">" . $row['title'] . " - " .$row['artist'] ."</span><label class=\"hidden\" value=\"".$row['productID'] ."\" name=\"productID\"></label>"; echo "<br />" . $row['description'] . "<br /></td></tr>"; } ?> </table> </div> <!-- InstanceEndEditable --> <?php include('includes/footer.inc'); ?> </body> <!-- InstanceEnd --></html> Quote Link to comment Share on other sites More sharing options...
bluesoul Posted January 24, 2009 Share Posted January 24, 2009 Replace your query with this and rerun it and see where the error is. $result = mysql_query("SELECT * FROM tProducts WHERE artist='$cd_artist', title='$cd_title', genre='$cd_genre', year='$cd_year', type='$cd_type'") or die(mysql_error()); But, to the point, you need to replace your commas with the AND statement. Quote Link to comment Share on other sites More sharing options...
Sarajs Posted January 24, 2009 Author Share Posted January 24, 2009 "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 ' title='', genre='', year='', type=''' at line 1" am I not calling the variables in the correct way? Quote Link to comment Share on other sites More sharing options...
bluesoul Posted January 24, 2009 Share Posted January 24, 2009 Right. A good tip with mysql_error() is to look immediately before the error string it provides, which would be your comma. Commas work in UPDATE statements across multiple fields but to qualify a search result you need to use WHERE artist='$cd_artist' AND title='$cd_title' AND...etc. Quote Link to comment Share on other sites More sharing options...
Sarajs Posted January 24, 2009 Author Share Posted January 24, 2009 Brilliant that worked a treat, thanks for the heads up! Now.... I have another question! :S I'm trying to to a search type thing but I can't figure out how to search with only entering one value in one of the search boxes: http://saras.ccacolchester.com/webprog/SoundsGood/webdevstie/search.php Sorry to be a pain! Quote Link to comment Share on other sites More sharing options...
Sarajs Posted January 24, 2009 Author Share Posted January 24, 2009 is there an and or operator I can use in mysql? Quote Link to comment Share on other sites More sharing options...
9three Posted January 24, 2009 Share Posted January 24, 2009 Your logic code would be: If user hit search button then ->grab field1, field2, field3, field4, field5 ->run query ->display results if (is_set($_POST['submit'])) { //Sanitise all possible inputs $artist = mysql_real_escape_string($_POST['artist']); $genre = mysql_real_escape_string($_POST['genre']); $type = mysql_real_escape_string($_POST['type']); $title = mysql_real_escape_string($_POST['title']); $year = mysql_real_escape_string($_POST['year']); $query = "SELECT artist, genre, type, title, year FROM data WHERE artist = $artist, genre = $genre, type = $type, year = $year"; $execute = mysql_query($query); //We're going to display the data if ($field = mysql_fetch_array($execute) >== 1) { //I'll leave the design up to you while ($field) { $displayartist = $field['artist']; echo $displayartist; } } else { echo 'Your search returned zero results.'; } the clause of FROM x depends on the name of the table in your data base. If the name is music, then it would be FROM music. Remember that they are case sensitive. I haven't tested this, but mess with it. It should give you something similar to what you asked. Also, add the name = "submit" to your search button. this wont run without it Quote Link to comment Share on other sites More sharing options...
jscix Posted January 24, 2009 Share Posted January 24, 2009 Yep there is. http://dev.mysql.com/doc/refman/5.1/en/logical-operators.html#operator_or Select * FROM artists where `band` LIKE 'portishead' OR `band` like 'nine inch nails'; Sorry-- Just re-read your post. Yes there is also an XOR operator. Which I believe is just that, 'XOR' Quote Link to comment Share on other sites More sharing options...
Sarajs Posted January 24, 2009 Author Share Posted January 24, 2009 Your logic code would be: If user hit search button then ->grab field1, field2, field3, field4, field5 ->run query ->display results the clause of FROM x depends on the name of the table in your data base. If the name is music, then it would be FROM music. Remember that they are case sensitive. I haven't tested this, but mess with it. It should give you something similar to what you asked. Also, add the name = "submit" to your search button. this wont run without it But would this not make the search results rely on every field being filed in on the form? The form works when I put all of the correct information into the fields e.g: http://saras.ccacolchester.com/webprog/SoundsGood/webdevstie/search.php artist = "Lily Allen" Genre = "Pop" Type = "CD" Year = "2008" Title = "Alright Now" I'm confussled! :S Quote Link to comment Share on other sites More sharing options...
uniflare Posted January 24, 2009 Share Posted January 24, 2009 You need to use wildcards: Select * FROM artists where `band` LIKE '%portishead%' OR `band` like '%nine inch nails%'; Quote Link to comment Share on other sites More sharing options...
uniflare Posted January 24, 2009 Share Posted January 24, 2009 <?php mysql_query("SELECT * FROM tProducts WHERE artist='$cd_artist', title='$cd_title', genre='$cd_genre', year='$cd_year', type='$cd_type'"); ?> should be: <?php mysql_query("SELECT * FROM tProducts WHERE `artist` LIKE '%".$cd_artist."%' OR `title` LIKE '%".$cd_title."%' OR `genre` LIKE '%".$cd_genre."%' OR `year` LIKE '%".$cd_year."%' OR `type` LIKE '%".$cd_type."%'; "); ?> Quote Link to comment Share on other sites More sharing options...
9three Posted January 24, 2009 Share Posted January 24, 2009 when you use POST submit it runs the code whether there is something in the field or not. As uniflare said, you can use wild cards. It will actually be better. Quote Link to comment Share on other sites More sharing options...
Sarajs Posted January 24, 2009 Author Share Posted January 24, 2009 This just shows everything all the time?! Quote Link to comment Share on other sites More sharing options...
uniflare Posted January 24, 2009 Share Posted January 24, 2009 because if you dont put something in 1 of the fields, in the query will be left with just wildcards (which matches everything). do something like: if($cd_artist == ""){ $cd_artist = "NULLSEARCHFIELD"; // Should not match anything } if($cd_year == ""){ $cd_artist = "NULLSEARCHFIELD"; // Should not match anything } ... mysql_query(...); Quote Link to comment Share on other sites More sharing options...
Sarajs Posted January 24, 2009 Author Share Posted January 24, 2009 because if you dont put something in 1 of the fields, in the query will be left with just wildcards (which matches everything). do something like: if($cd_artist == ""){ $cd_artist = "NULLSEARCHFIELD"; // Should not match anything } if($cd_year == ""){ $cd_artist = "NULLSEARCHFIELD"; // Should not match anything } ... mysql_query(...); Think I might put this one to my tutor, I'm getting so confused for some reason! :-\ I use asp.net at work and it's really hard for me to change. Thank you all ever so much for helping with previous issues and trying to help me with this one. I don't want to be a burden anymore! Quote Link to comment Share on other sites More sharing options...
uniflare Posted January 24, 2009 Share Posted January 24, 2009 lol np, these forums are to help! lol, we help you, dont say sorry for needing our help, we want to help! lol! anyway: try this last attempt if you can be bothered : <?php if($cd_artist == ""){ $cd_artist = "NULLSEARCHFIELD"; // Should not match anything } if($cd_title == ""){ $cd_title = "NULLSEARCHFIELD"; // Should not match anything } if($cd_genre == ""){ $cd_genre = "NULLSEARCHFIELD"; // Should not match anything } if($cd_year == ""){ $cd_year = "NULLSEARCHFIELD"; // Should not match anything } if($cd_type == ""){ $cd_type = "NULLSEARCHFIELD"; // Should not match anything } mysql_query("SELECT * FROM tProducts WHERE `artist` LIKE '%".$cd_artist."%' OR `title` LIKE '%".$cd_title."%' OR `genre` LIKE '%".$cd_genre."%' OR `year` LIKE '%".$cd_year."%' OR `type` LIKE '%".$cd_type."%'; "); ?> Quote Link to comment Share on other sites More sharing options...
9three Posted January 24, 2009 Share Posted January 24, 2009 I would use switch instead of all those if conditions Quote Link to comment Share on other sites More sharing options...
uniflare Posted January 24, 2009 Share Posted January 24, 2009 how would you use a switch? Quote Link to comment Share on other sites More sharing options...
nuttycoder Posted January 24, 2009 Share Posted January 24, 2009 an example from php.net <?php switch ($i) { case 0: echo "i equals 0"; case 1: echo "i equals 1"; case 2: echo "i equals 2"; } ?> http://uk2.php.net/switch I don't think you need a switch in your case as each value has only one outcome. Also searching using like is not all that accurate a better way is using match this may be useful if you ever want to go down that route : http://www.newmedia.lincoln.ac.uk/dcarr/tutorials/php/searching-with-php-and-mysql-beyond-like Quote Link to comment Share on other sites More sharing options...
uniflare Posted January 24, 2009 Share Posted January 24, 2009 Thats exactly my point , i was asking how a switch statement could possibly work in this situation. I know how a switch statement works . Quote Link to comment Share on other sites More sharing options...
nuttycoder Posted January 24, 2009 Share Posted January 24, 2009 My bad :-X Quote Link to comment Share on other sites More sharing options...
uniflare Posted January 24, 2009 Share Posted January 24, 2009 np, good idea with the match though. havn't seen that before . Quote Link to comment Share on other sites More sharing options...
9three Posted January 24, 2009 Share Posted January 24, 2009 I meant that for best practices, switch would be better. I also noticed a typo in my code from earlier in the is_set. Should be isset instead Quote Link to comment Share on other sites More sharing options...
nuttycoder Posted January 24, 2009 Share Posted January 24, 2009 I find its a really good way of searching I've used it ever since I came across it. Quote Link to comment Share on other sites More sharing options...
uniflare Posted January 24, 2009 Share Posted January 24, 2009 Yes, If you can use a switch statement rather then an if/elseif/else statement, then you should. Unfortunately it does not apply in this case . 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.