PLease Help with my error

Sarajs · 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>

bluesoul · 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.

Sarajs · 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?

bluesoul · 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.

Sarajs · 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!

Sarajs · January 24, 2009

is there an and or operator I can use in mysql?

9three · 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

jscix · 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'

Sarajs · 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

uniflare · January 24, 2009

You need to use wildcards:

Select * FROM artists where `band` LIKE '%portishead%' OR `band` like '%nine inch nails%';

uniflare · 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."%';
");

?>

9three · 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.

Sarajs · January 24, 2009

This just shows everything all the time?!

uniflare · 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(...);

Sarajs · 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!

uniflare · 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."%';
");

?>

9three · January 24, 2009

I would use switch instead of all those if conditions

uniflare · January 24, 2009

how would you use a switch?

nuttycoder · 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

uniflare · 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 .

nuttycoder · January 24, 2009

My bad :-X

uniflare · January 24, 2009

np, good idea with the match though. havn't seen that before .

9three · 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

nuttycoder · January 24, 2009

I find its a really good way of searching I've used it ever since I came across it.

uniflare · 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 .

Sign In

PLease Help with my error

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information