Query On Connection Failed

jennifer_ann

Does anyone know what an error of

"Query on connection Failed" means?????

Houdini

Just means that you have a connection but the query failed for some reason or the other.

bpat1434

Houdini beat me to it....

But without code, we can't tell you any more than what it says...

jennifer_ann

[code=php]<html>

<head>
	<title>Eye Contact - Century to Century</title>
	<link rel="stylesheet" href="eye01.css" type="text/css" />
</head>

<?php
$db = mysql_connect("localhost","root","");
if (!$db){
		print "error - Could not connect to MySQL";
		exit;
}

$er = mysql_select_db("touch_art1");
if(!$er){
	print "error - Could not select the database";
		exit;	
}

//Set variables
$yr_left = "Year Left";
$city_origin = "City Origin";
$country_origin = "Country Origin";
$yr_arrival = "Year Arrived";
$city_arrival = "City Arrived";
$state_arrival = "State Arrived";


$query = "SELECT yr_left, city_origin, country_origin, yr_arrival, city_arrival, state_arrival, filename FROM story WHERE pamapproved = 'Y' and yr_left = $yr_left ORDER by yr_left";
$result = mysql_query($query);

//check to see if query worked
if (!$result)
{
	echo 'Query on Connection failed!';
	// exit	
}
else
{
	echo 'succees';
	// process results

while($row = mysql_fetch_row($result)) {
$date = $row[1];
echo '$date'; 
$name =$row[2];
echo'$name';

}
}
?>

</html>

I'm trying to query the date left. someone will type in what year they left, and I want to bring up all results according to that year.

bpat1434

Okay, so here's your problem: You don't specify when the $yr_left is. You say $yr_left = 'Year Left'; but not a $_POST['yr_left'] or anything that says a user input that information. So the query you run is this:

SELECT yr_left, city_origin, country_origin, yr_arrival, city_arrival, state_arrival, filename 
FROM story 
WHERE pamapproved = 'Y' 
  AND yr_left = Year Left 
ORDER by yr_left

which is wrong for two reasons:
1.) You need to encase the $yr_left variable in single quotes so that the mySQL interpreter reads it as a string, not as possibly part of the command if there are spaces (like now).
2.) The value you give $yr_left is not the type of value (string, integer) that mySQL is expecting.

I'm guessing on the second. But I bet I'm right.

jennifer_ann

I'm sorry I'm not sure I understand.

bpat1434

someone will type in what year they left

Where do they do this?

$yr_left = "Year Left";

This is a string

$query = "SELECT yr_left, city_origin, country_origin, yr_arrival, city_arrival, state_arrival, filename FROM story WHERE pamapproved = 'Y' and yr_left = $yr_left ORDER by yr_left";

This will not execute because you don't encapsulate the string $yr_left in quotes in the query (like you did with pamapproved). So the query you get looks like this:

SELECT yr_left, city_origin, country_origin, yr_arrival, city_arrival, state_arrival, filename 
FROM story 
WHERE pamapproved = 'Y' 
  AND yr_left = Year Left 
ORDER by yr_left

Note: Broken up for aesthetics

and so when mySQL looks at the query, it will see:

WHERE pamapproved='Y' AND yr_left='Year' LEFT ORDER BY

so it should interpret the "Left" as a command, and if it does, it's wanting a specific syntax with it (Join for instance) and it's not seeing it. Thus, you get a mySQL error. And your query doesn't work.

Now, since in your code you thought "What if the query fails? I'll need to let the user know!!" you put in some error checking:

if (!$result)
{
    echo 'Query on Connection failed!';
    // exit    

}

Which says that if there is no result (or a proper result resource ID returned by the query) to print on screen "Query on Connection failed!". Not very helpful. Obviously, this is not very informative; however to the end user it's fine. But for now, we need more info. So perhaps you should say something like:

if(!$result)
{
  die('Query on mySQL database failed.  The error returned was:<br>'.mysql_error());
}

That will give you information about the query you ran and what mySQL is having trouble with.

Now, before you do that, I suggest modifying your query from:

$query = "SELECT yr_left, city_origin, country_origin, yr_arrival, city_arrival, state_arrival, filename FROM story WHERE pamapproved = 'Y' and yr_left = $yr_left ORDER by yr_left";

$query = "SELECT yr_left, city_origin, country_origin, yr_arrival, city_arrival, state_arrival, filename FROM story WHERE pamapproved = 'Y' and yr_left = '$yr_left' ORDER by yr_left";

But that still doesn't take into account the type of field (number or text) yr_left is. If it's a numerical field (INT, LONG INT, YEAR) then mySQL won't return any rows from your query if you pass it a string (like "Year Left"). Understand?

jennifer_ann

I understand a lot more clearly now. And yes I'm going to have Year Left as integer. So I need to make it 'year left'?

bpat1434

yes.... but you also need to make it a number (like 2006, 1994, etc.).

jennifer_ann

Can I make year a variable so not matter what year they input on my webpage it will query that particular year?

bpat1434

yes, just set it like you have already.

$yr_left = '2006';

jennifer_ann

So can I leave it $yr_left = ''; And that will allow people to put in whatever they want?

bpat1434

no, you need a form to get user input....

jennifer_ann

Thank you very much. I REALLY appreciate it!