Query returns rows that are empty??

[nullpoint81]

This may even be more of a SQL question, but you guys are good so I thought I'd give you a crack at this first.

 

I have this query:

 

$query = "SELECT * FROM $day,sip_data WHERE $day.id=sip_data.id AND $day$time<=>$day$time1 ORDER BY zip ASC";

 

Which searches between different tables on the same database, basically matching up the $id on tables and giving the results.

 

Funny thing is, when the search is executed, it displays the name of the sip.data table even if the row is completely empty (i.e. - if the matching $day$time row is completely empty). I suspect this is because when something is added to the DB, an $id (AUTO_INCREMENT) is added to all tables and rows.

 

I've got all of the rows set to NULL by default, TEXT as type, except for the $id on each table, which is AUTO_INCREMENT.

 

Any suggestions?

[gin]

Use a LEFT JOIN. Something like so:

SELECT * FROM $day LEFT JOIN sip_data ON $day.id = sip_data.id ORDER BY zip

 

This will make data on the right table only appear if there's data on the left table.

[fenway]

Actually, that's what an INNER JOIN would do.

[gin]

Oh, my mistake, I misread the question

[nullpoint81]

Upon further testing the issue still remains. It's very...strange.

 

The LEFT JOIN and INNER JOIN didn't work - actually gave the same exact results. I was looking at what tables had data in my testDB and discovered that this query is actually returning all results (obviously, since there is a *). The really weird part is that when it hits data in a table, it will actually exclude that result entirely. So it's kinda doing the opposite of what it's supposed to be doing (in some cases)!

 

Another example is...let's say I have a partial match - data is on 1/2 of the query but not on the other half (1/2 of the time range, for example). It will show that result as if it was a full match.

 

 

[nullpoint81]

One other thing I should mention - the $day, $time, $time1 are variables populated by a form (ajax), so they must be included for basically anything to work.

 

It's essentially a form that takes the $day (monday-sunday) and $time,$time1 (military - 0800 to 0200). The query is supposed to search for results in between $time and $time1 (whatever the user selects).

 

Here's the current query code (added a filter for $type):

 

$query = "SELECT * FROM $day,sip_data WHERE sip_data.type='bar' AND $day.id=sip_data.id AND $day.$day$time <=> $day.$day$time1 ORDER BY zip ASC";

 

The ASC is kind of redundant, I know. I'm still curious if this is an issue with the query itself or the way the DB is set up...puzzled/mindblown.

[nullpoint81]

I really do need a pro at this point. Not trying to bump or be a Bueller, just trying to get this resolved before I smoke the entire pack.

[nullpoint81]

Full page code:

 

<?php
$dbhost = "llllocalhost!";
$dbuser = "lll";
$dbpass = "lll";
$dbname = "lll";

//Connect to MySQL Server
$link = mysql_connect($dbhost, $dbuser, $dbpass);

//Select Database
mysql_select_db($dbname) or die(mysql_error());

// Retrieve data from Query String
$id = $_GET['id'];
$name = $_GET['name'];
$zip = $_GET['zip'];
$server_url = $_GET['server_url'];
$day = $_GET['day'];
$time = $_GET['time'];
$time1 = $_GET['time1'];
$type = $_GET['type'];

// Escape User Input to help prevent SQL Injection
$id = mysql_real_escape_string($id);
$name = mysql_real_escape_string($name);
$zip = mysql_real_escape_string($zip);
$server_url = mysql_real_escape_string($server_url);
$day = mysql_real_escape_string($day);
$time = mysql_real_escape_string($time);
$time1 = mysql_real_escape_string($time1);
$type = mysql_real_escape_string($type);
//build query

$query = "SELECT * FROM $day,sip_data WHERE sip_data.type='bar' AND $day.id=sip_data.id AND $day.$day$time <=> $day.$day$time1 ORDER BY zip ASC";

//Execute query
$qry_result = mysql_query($query) or die(('No Results'));

//Build Result String

$places = array();
while($row = mysql_fetch_array($qry_result))
{
$places[$row['zip']][$row['server_url']] = $row['name'];
}


foreach ($places as $place => $d)
{
	echo "<table id=query_result align=left>";
	echo "<tr>";
	echo "<th id=query_result_zip>$place</th>";
	echo "</tr>";
	foreach ($d as $n => $r)
	{
	echo "<tr>";
	echo "<td id=query_result_name><a href=$n rel=ajaxDiv>$r</a></td>";
	echo "</tr>";

	}

}

echo "</table>";	

?>

 

Carryover from previous thread, this is baffling me yet again.

[kickstart]

Hi

 

What confuses me is that you appear to be passing in a parameter as one of the table names AND the part of the field names (and worryingly, as a field from an input form).

 

First thing I would suggest is to echo out the SQL before you execute it.

 

Could you also post the table layouts and some sample data? If so I can try and have a play.

 

Part of the problem might be that you are using <=> . This will equate to true if both items are NULL. But for that to cause an issue I would expect there to still need to be a records on the table with a matching id.

 

All the best

 

Keith

[nullpoint81]

Part of the problem might be that you are using <=> . This will equate to true if both items are NULL. But for that to cause an issue I would expect there to still need to be a records on the table with a matching id.

 

That sounds like the case, indeed, for these reasons:

[*]I have empty rows throughout the DB (since some corresponding times do not have anything valuable to use in the way of data).

[*]The query is essentially saying match the id from the $day.$day$time table to the corresponding id on the sip_data table.

 

I've tried changing up the query a bit, tried a BETWEEN in place of AND, all to no avail.

 

When you say "echo out the SQL", what do you mean exactly?

 

Thanks for the help.

[kickstart]

That sounds like the case, indeed, for these reasons:

[*]I have empty rows throughout the DB (since some corresponding times do not have anything valuable to use in the way of data).

[*]The query is essentially saying match the id from the $day.$day$time table to the corresponding id on the sip_data table.

 

I would avoid have null rows. However even a null row shouldn't match unless the both the $day.$day$time and $day.$day$time1 columns are null

 

I've tried changing up the query a bit, tried a BETWEEN in place of AND, all to no avail.

 

When you say "echo out the SQL", what do you mean exactly?

 

Thanks for the help.

 

Ie, something like this:-

 

$query = "SELECT * FROM $day,sip_data WHERE sip_data.type='bar' AND $day.id=sip_data.id AND $day.$day$time <=> $day.$day$time1 ORDER BY zip ASC";

echo "$query <br />";

 

So we know what it has given after substituting the variables for their values.

 

The idea of having multiple tables for different days, and columns split down similarly is a bit strange.

 

All the best

 

Keith

[PFMaBiSmAd]

Slightly off topic, but when your table and column names are from external data, escaping the values won't prevent sql injection, because the point of escaping string data values is so that you cannot break out of (escape from) single-quotes in the query. Since table and column names (and numerical data values) are not surrounded by single-quotes in the query, the type of sql injection that does not use any quotes in it, can still be used in table and column names (and numerical data values.)

 

If table and column names come from external data, you must validate that they only contain expected and valid table and column names before you put them into the query.

 

-----------------------

 

As to your database design. Databases are not spreadsheets and trying to treat them as spreadsheets (i.e. finding data among a bunch of empty 'cells' in each row) results in a lot of extra code and complicated, dynamically produced, queries. You need to forget about having same meaning data spread out in a bunch of different tables that only differ in the table name and forget about having a bunch of same meaning columns spread out in a row that only differ in the column name and put each piece of same meaning data into one table, with one row per separate piece of data, so that your queries can easily find the relevant data that you need.

[nullpoint81]

As to your database design. Databases are not spreadsheets and trying to treat them as spreadsheets (i.e. finding data among a bunch of empty 'cells' in each row) results in a lot of extra code and complicated, dynamically produced, queries. You need to forget about having same meaning data spread out in a bunch of different tables that only differ in the table name and forget about having a bunch of same meaning columns spread out in a row that only differ in the column name and put each piece of same meaning data into one table, with one row per separate piece of data, so that your queries can easily find the relevant data that you need.

 

Well, I guess I can't necessarily visualize what you mean by this, although looking at the way that a CMS...let's say Wordpress, for example, handles data I do agree. I may not be doing this the most efficient way possible.

 

If you're interested, I could let you see what I'm working on.