Incorrect query?

3raser · September 7, 2011

It's been a while since I've used my queries, can anyone tell me what's wrong with this mysql query?

$query = mysql_query("SELECT * FROM `chicken_names` WHERE `gender` = 'Hen' AND `name` LIKE '%$s_terms%' ORDER BY `likes` DESC") or die(mysql_error());

nilansanjaya · September 7, 2011

are you getting a php error or a mysql error ?

JKG · September 7, 2011

no sql error...

Nodral · September 7, 2011

Try

$query = mysql_query("SELECT * FROM `chicken_names` WHERE `gender` = 'Hen' AND `name` LIKE '%".$s_terms."%' ORDER BY `likes` DESC") or die(mysql_error());

If it still doesn't work, post your error

Maq · September 7, 2011

What makes you think it's wrong? What does it do? What do you want it to do? Does your page break? Blank page? Error? No results? Unexpected results? What?!

3raser · September 10, 2011

Sorry, my error:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /www/zxq.net/c/h/i/chickennames/htdocs/index.php on line 307

Code:

<?php

mysql_connect("localhost", "removed", "removed");
mysql_select_db("removed");

?>
<html>
<head>
<title>Chicken Names! - Hundreds of 'em!</title>
<meta name="description" content="Chicken Names is the best place to go when your in need for names for your chickens. We currently have around
500 Hen and Rooster names and growing! Check our Chicken Names today!" />
<style>
body {
font-family: Times New Roman;
}

h2 {
margin:0px;
}

.bce {
float:left;
}

.stats_right {
float:right;
}

#box {
padding:3px;
width:458px;
text-align:left;
border:2px solid black;
background-color:white;
}

#nav {
padding:5px;
width:448px;
height:21px;
text-align:left;
background-color:black;
color:white;
}

#nav a:hover, a:link, a:active, a:visited {
color:white;
font-weight:bold;
text-decoration:none;
}

td {
padding:7px;
margin:2px;
}

th {
padding:7px;
margin:2px;
}
</style>
</head>
<body>

<div class="bce">
<a href="http://www.backyardchickens.com/forum/viewtopic.php?id=481698&p=1"><img src="http://www.backyardchickens.com/images/forum-header-byc-only.gif"></a>
</div>

<center>

<div id="box">
<div id="nav"><a href='index.php'>Home</a> | <a href='index.php?add=1'>Submit</a> | <A href='index.php?order=bylikes'>Order by Likes</a> 
| <a href='index.php?search=1'>Search</a></div>

Welcome to my simple & easy chicken names website! Here you can browse for the perfect chicken name, or submit
your very own!<br/><br/><b>Facts/Stats:</b><br/>

<?php
	//grab stats
	$one = mysql_query("SELECT * FROM chicken_names");
	$two = mysql_query("SELECT * FROM likes_new");

	$three = mysql_query("SELECT name FROM chicken_names ORDER BY likes DESC LIMIT 1");
	$ex_n3 = mysql_fetch_assoc($three);

	echo mysql_num_rows($one)." names in our database.<br/>";
	echo mysql_num_rows($two)." names have been liked.<br/>";
	echo "Highest liked name: <b><span style='color:red'>". $ex_n3['name']. "</span></b>";
?>
<br/><hr><br/>

<?php

	//global variables
	$_like = $_GET['like'];

	//type of search
	$order = mysql_real_escape_string($_GET['order']);

	//grab their ip
	$ip = $_SERVER['REMOTE_ADDR'];

	//lets grab our views
	$file = "includes/views.txt";
	$fopen = fopen($file, 'r');
	$views = fread($fopen, 10);
	fclose($fopen);


	//calculate new views
	$new_views = $views + 1;

	//write new views to file
	$fopen = fopen($file, 'w');
	fwrite($fopen, $new_views);
	fclose($fopen);

//check if they are submitting names, if not, return them to the viewing (or like) page
if(!$_GET['add'] && !$_POST['name'])
{

	if($_like && !$_GET['add'])
	{

		//make like safer
		$_like = mysql_real_escape_string($_like);

		//make query to check state
		$check_ex = mysql_query("SELECT id FROM chicken_names WHERE id = '$_like' LIMIT 1");

		//check if it exists
		if(mysql_num_rows($check_ex) < 1)
		{
			echo "You can't like a name that doesn't exist!";
		}
		else
		{
			//mysql variables
			$extract_n_info = mysql_query("SELECT name FROM chicken_names WHERE id = '$_like' LIMIT 1");
			$return_info = mysql_fetch_assoc($extract_n_info);

			$extract_existing = mysql_query("SELECT * FROM likes_new WHERE n_id = '$_like' AND ip = '$ip' LIMIT 1");

			if(mysql_num_rows($extract_existing) >= 1)
			{
				echo "I'm sorry, you have already like this name.";
			}
			else
			{
				echo "You have successfully liked the name <b>". $return_info['name'] ."!</b>";

				//register they liked this name
				mysql_query("INSERT INTO likes_new VALUES (null, '$_like', '$ip')");

				//update the like count for that name
				mysql_query("UPDATE chicken_names SET likes = likes + 1 WHERE id = '$_like'");
			}
		}
	}
	elseif($_GET['search'])
	{
		?>

			<form action="index.php" method="POST">
			<input type="hidden" name="search" value="yes">
			<table>
				<b>Would you like to order the names by likes?</b></br>
				<input type="checkbox" name="i_likes" value="Yes">Yes, I want to order the names by likes.

				<br/><br/>

				<b>Order by gender?</b>
				<tr>
					<td><input type="radio" name="gender" value="Both">Both</td>
					<td><input type="radio" name="gender" value="Rooster">Rooster</td>
					<td><input type="radio" name="gender" value="Hen">Hen</td>
				</tr>
				<tr>
					<td>Keywords</td>
					<td><input type="text" name="search_terms" maxlength="200"></td>
				</tr>
				<tr>
					<td>Done?</td>
					<td><td><input type="submit" value="Start Search"s></td></td>
				</tr>
			</table>
			</form>

		<?php
	}
	else
	{		

		//do we do a normal extract or special
		if($_POST['search'])
		{
			//search related variables
			$s_gender = $_POST['gender'];
			$s_type = $_POST['i_likes'];
			$s_terms = $_POST['search_terms'];

			if($s_gender == "rooster" && !empty($s_type))
			{
				$query = mysql_query("SELECT * FROM `chicken_names` WHERE `gender` = 'Rooster' AND `name` LIKE '%".$s_terms."%' ORDER BY `likes` DESC") or die(mysql_error());
			}
			elseif($s_gender == "hen" && !empty($s_type))
			{
				$query = mysql_query("SELECT * FROM `chicken_names` WHERE `gender` = 'Hen' AND `name` LIKE '%".$s_terms."%' ORDER BY `likes` DESC") or die(mysql_error());
			}
			elseif($s_gender == "both" && !empty($s_type))
			{
				$query = mysql_query("SELECT * FROM `chicken_names` WHERE `name` LIKE '%".$s_terms."%' ORDER BY `likes` DESC") or die(mysql_error());
			}
			elseif($s_gender == "rooster" && empty($s_type))
			{
				$query = mysql_query("SELECT * FROM `chicken_names` WHERE `gender` = 'Rooster' AND `name` LIKE '%".$s_terms."%' ORDER BY `id` DESC") or die(mysql_error());
			}
			elseif($s_gender == "hen" && empty($s_type))
			{
				$query = mysql_query("SELECT * FROM `chicken_names` WHERE `gender` = 'Hen' AND `name` LIKE '%".$s_terms."%' ORDER BY `id` DESC") or die(mysql_error());
			}
			elseif($s_gender == "both" && empty($s_type))
			{
				$query = mysql_query("SELECT * FROM `chicken_names` WHERE `name` LIKE '%".$s_terms."%' ORDER BY `id` DESC") or die(mysql_error());
			}
			else
			{
				//empty search, no query to set
			}

			search($query);
		}
		else
		{
			if($order == "bylikes")
			{
				$extract_names_no_order = mysql_query("SELECT * FROM chicken_names ORDER BY likes DESC");
			}
			else
			{
				$extract_names_no_order = mysql_query("SELECT * FROM chicken_names ORDER BY id DESC");
			}
		}

		search($extract_names_no_order);
	}
}
else
{

	if(!$_POST['name'] || !$_POST['gender'])
	{
		?>
			<b>NOTICE: Please make sure you read this before submitting your chicken names! You can submit multiple names at a time by seperating all names
			with a comma. But, if you're submitting multiple names at a time: Please make sure when you select the gender that ALL your names are
			for that gender only.<br/><br/></b>
			<br>
			<form action="index.php" method="POST">
			<table>

				<tr>
					<td>Chicken Name(s)</td><td><textarea name="name" maxlength="750" cols="45" rows="20"></textarea></td>						
				</tr>

				<tr>
					<td>Gender</td><td><select name="gender"><option value="Hen">Hen</option><option value="Rooster">Rooster</option></select></td>
				</tr>

				<tr>
					<td>Done?</td><td><input type="submit" value="Submit names!"></td>
				</tr>

			</table>
			</form>

		<?php
	}
	else
	{
		//make variables safe
		$date = date('m-d-y');
		$name = mysql_real_escape_string($_POST['name']);
		$gender = mysql_real_escape_string($_POST['gender']);

	    //explode names
		$names_exp = explode(",", $name);

		foreach($names_exp as $value)
		{
			mysql_query("INSERT INTO chicken_names VALUES (null, '$value', '$gender', '0','$date', '$ip')");
		}

		echo "You have successfully registered your chicken names! Names added:<br/><br/>";

		foreach($names_exp as $value)
		{
			echo "<b>". $value. "</b><br/>";
		}

	}

}

function search($query)
{
	//get and display chicken names
		while($row = mysql_fetch_assoc($query))
		{

			echo "
				<center>
				<table>

					<tr>
						<th>Name</th>
						<th>Gender</th>
						<th>Likes</th>
						<th>Like this name?</th>
					</tr>
					<tr>

						<td><span style='color:red'>". $row['name'] ."</span></td>
						<td>". $row['gender'] ."</td>
						<td>". $row['likes'] ."</td>
						<td><a href='index.php?like=". $row['id'] ."'><span style='color:green'>Like</span></a></td>
					</tr>
				</table>
				<hr>
				</center>
			";
		    
		}
}
?>

</div>
</center>
</body>
</html>

Maq · September 11, 2011

Is this line 307?

$ex_n3 = mysql_fetch_assoc($three);

The error usually means your mysql_query returned false because your query is invalid. Change this line to:

$three = mysql_query("SELECT name FROM chicken_names ORDER BY likes DESC LIMIT 1") or die(mysql_error());

Sign In

Incorrect query?

Recommended Posts

3raser

Link to comment

Share on other sites

nilansanjaya

Link to comment

Share on other sites

JKG

Link to comment

Share on other sites

Nodral

Link to comment

Share on other sites

Maq

Link to comment

Share on other sites

3raser

Link to comment

Share on other sites

Maq

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information