Showing No results for query

[Xtremer360]

I'm having trouble with my query. What I"m wanting to do is have it say the event name, the booking date, and the location of the event and have it have it limit to the next 5 events with a status id of 3. All the main info is coming from my efed_content_booking table.  To get the show name you have to take the event_id from efed_content_booking and use that with efed_list_shownames and match that against the id and then take the value from the name field and that becomes the name of the event.

 

Right now it shows Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource.

 

function eventinfo($id){
$query = "SELECT ecb.status_id AS statusid, ecb.bookingdate AS bookingdate, ecb.location AS location FROM `efed_content_booking` AS ecb INNER JOIN `efed_list_shownames` AS els ON ecb.event_id = els.id WHERE ecb.event_id = '$id' AND ecb.status_id = '3'  ORDER BY ecb.bookingdate LIMIT 5";
if(!$result = mysql_query ($query))
if(mysql_num_rows($result) == 0){
	print '<p>Currently no events are booked.</p>';
}
else{
	while ($row = mysql_fetch_assoc($result)){
		$fieldarray=array('name','bookingdate','location','arena');
		foreach ($fieldarray as $fieldlabel){
			if (isset($row[$fieldlabel])){ 
				$$fieldlabel=$row[$fieldlabel];
				$$fieldlabel=cleanquerydata($$fieldlabel);
			}
		}
	}
	$bookingdate = convertdate($bookingdate);
	print "<strong><span>" . $showname . "</span></strong>";
	print "<strong>Date</strong><br />" . $bookingdate . "<br /><strong>Location</strong><br />" . $location . "<br /><strong>Arena</strong><br />" . $arena;

}
}

[Psycho]

The logic is messed up

	if(!$result = mysql_query ($query))
if(mysql_num_rows($result) == 0){

 

Basically, that is saying IF the query fails then check IF the number of results equals 0. If the query fails you can't check if any results were returned. You need to fix that logic as well as fix your query (which is failing).

[Psycho]

There is definitely something wrong with your query.

SELECT ecb.status_id AS statusid, ecb.bookingdate AS bookingdate, ecb.location AS location
FROM `efed_content_booking` AS ecb
  INNER JOIN `efed_list_shownames` AS els ON ecb.event_id = els.id
WHERE ecb.event_id = '$id'
  AND ecb.status_id = '3'
ORDER BY ecb.bookingdate
LIMIT 5

 

You are joining the `efed_list_shownames`, but you are not using it - at all. There are no values from that table selected in the SELECT clause, there are no values used by that table in the WHERE clause and no values from that table in the ORDER by clause. In other words, that JOIN does absolutely nothing.

 

There are also other problems in the logis to process/display the query results. But, assuming this is a homework problem I don't want to provide too much help.

[Xtremer360]

$query = "SELECT ecb.bookingdate AS bookingdate, ecb.location AS location els.name FROM `efed_content_booking` AS ecb INNER JOIN `efed_list_shownames` AS els ON ( ecb.event_id = els.id ) WHERE ecb.status_id = '3' ORDER BY ecb.bookingdate LIMIT 5";

 

I fixed it and have this now. However still getting the same thing. And it's not homework. Its for my website that isn't working straight.

[Xtremer360]

Woudl this be okay for doing for the login of the query:

 

$result = mysql_query ( $query ); 
        $rows = mysql_num_rows($result);
    if ($rows =  0)

[Psycho]

First off, have you tested the query in phpmyadmin to ensure it is returning results? For testing purposes you should always be validating that your query is running correctly and, if not, checking the errors. You're not even checking for errors.

 

Also, whay are you querying fields and using AS to give them the same name they already have? You only need to do that if you are pulling two fields with the same name so thay have unique names in the result set. Or, you might do that if the value is being used in the script for a different purpose than the name would apply. In this query it is just making it difficult to read. however there is an error in your query. You are pulling three values in the SELECT clause and there is no comma between the second and third values. Here is what I would suggest

$query = "SELECT ecb.bookingdate, ecb.location, els.name
          FROM `efed_content_booking` AS ecb
            INNER JOIN `efed_list_shownames` AS els ON ( ecb.event_id = els.id ) 
          WHERE ecb.status_id = '3'
          ORDER BY ecb.bookingdate
          LIMIT 5";

 

So, now that you have a query that is more likely toi work, you should include proper error handling. This is just an example:

$result = mysql_query($query);

if (!$result)
{
    echo "The query failed!<br /><br />";
    echo "Query:<br />{$query}<br />";
    echo "Error:<br />" . mysql_error();
}
else if (mysql_num_rows($result)==0)
{
    echo "No results returned";
}
else
{
    //Process and display the results
}

 

Even if the query works now, try changing it so it will fail and you can see the error details that will be displayed.

 

By the way, this IF statement will always return true. A single equal sign is used for an assignment. So, since the processor will have no problem assinging a 0 to the variable $rows it will always return true. You need to use double equal signs for comparison.

if ($rows =  0)

[Xtremer360]

Thank you for all your help and teaching me a few extra things.

[RossF.72]

There are also other problems in the logis to process/display the query results. But, assuming this is a homework problem I don't want to provide too much help.

 

Its not homework, its reverse engineering the scrambled portions of a closed source script that he managed to steal from me.