Sql query not posting data

tigomark

Hello,

I am trying to pull info from a MySQL dbase with a simple script that recieves info from a get request such as

http://mysite/subfolder/index.php?where=Alaska

The code for the request is as follows

include "../includes/prefs.php";



$db_name = "weekends";

$table_name = "weekends";

$connection = @mysql_connect("$host", "$root", "$password") or
die("Couldn't connect.");

$db = @mysql_select_db($db_name, $connection) or die("Couldn't select database.");

	$sql = "SELECT *
  FROM $table_name
  WHERE State = \"$where\"   
  ORDER BY WeekendDate

	   $result = @mysql_query($sql,$connection) or die("Couldn't execute query.");

while ($row = mysql_fetch_array($result)) {


	$id = $row ['id'];
	$State = $row ['State'];
	$CityName = $row ['CityName'];
	$WeekendDate = $row ['WeekendDate'];
	$Email = $row ['Email'];
	$Phone = $row ['Phone'];
	$HostCouple = $row ['HostCouple'];




	$weekend_list .="


						<tr>
						<td align=\"center\"  valign=\"top\">$CityName</td>
						<td align=\"center\"  valign=\"top\">$WeekendDate</td>
						<td align=\"center\"  valign=\"top\">$Email</td>
						<td align=\"right\"  valign=\"top\">$Phone</td>
						<td align=\"center\"  valign=\"top\"><a href=\"/echopay/index.php?State=$where&id=$id\">Sign up Here</a></td>

						</tr>


	";

	}

//HTMl Output would then be in the section below

					<div id="you">
					<table width="100%" cellpadding="2" cellspacing="2" border="0" align="center">
					<tr>
					<td align="center" colspan="4"><h2><? echo "$State"; ?></h2></td>
					</tr>
					<tr>
					<th align="center"  valign="top">Location</th>
					<th align="center"  valign="top">Encounter Weekend Date</th>
					<th align="center"  valign="top">Email</th>
					<th align="center"  valign="top">Phone Number</th>
					<th align="center"  valign="top">&nbsp;</th>
					</tr>
					<? echo "$weekend_list"; ?>
					</table>
					</div>

When I test this on my test site and personal site I get the desired output for every state. When I put it to the clients server all that I see is the HTML which is just the Header information. I have never run into this and wanted to see if any one else has run into this.

nemonoman

Just a question: are you sure your client's site has php running?

echo "$weekend_list" --

You should at the very least be seeing a table with no values.

tigomark

I see the table data just not the data being pulled from the data base.

nemonoman

Then in all likelihood your DATA is the problem. You are apparently not seeing error messages, so check your data carefully. Does the data really show "Alaska"? Does it show "ALASKA" or 'alaska' or Akasla.

echo out the query, then run it directly against the database using phpMyAdmin or some similar tool.

Sxooter

$sql = "SELECT *
FROM $table_name
WHERE State = \"$where\"
ORDER BY WeekendDate";

Is wrong.

In SQL you quote data with ' not ". It should be:

$sql = "SELECT *
FROM $table_name
WHERE State = '$where'
ORDER BY WeekendDate";

then replace your or die("message"); bit with

or die(mysql_error($connection));

so you can see what error you're getting.

Sxooter

oh, and next time put your code in [ php ] tags, k? It makes it much more readable.

nemonoman

Sxooter wrote:
In SQL you quote data with ' not ".

This is a convention, maybe. Not a language rule.

Sxooter

no, it's a language rule. " is used to surround identifiers, like field and table names. ' is used to quote values. Period.

Note that MySQL decided to use ` to quote identifiers some time ago.

And it MIGHT let you use " to quote values.

But that's not what the standard say. And if you turn on sql mode they'll both break.

Sxooter

If you really want to look it up (warning, may cause insanity) sql 92 spec

nemonoman

From the MySQL language manual:

6 MySQL Language Reference
6.1.1 Literals: How to Write Strings and Numbers
6.1.1.1 Strings

A string is a sequence of characters, surrounded by either single quote ('') or double quote ("') characters (only the single quote if you run in ANSI mode). Examples:

'a string'
"another string"

There are several ways to include quotes within a string:

* A `'' inside a string quoted with `'' may be written as `'''.
* A `"' inside a string quoted with `"' may be written as `""'.
* You can precede the quote character with an escape character (`\').
* A `'' inside a string quoted with `"' needs no special treatment and need not be doubled or escaped. In the same way, `"' inside a string quoted with `'' needs no special treatment.

Note that if the identifier is a restricted word or contains special characters you must always quote it with a ` (backtick) when you use it:

mysql> SELECT * FROM select WHERE select.id > 100;

SQL is an abstact, not a language. Database developers create instances of the abstract and establish the rules of their query language. In MySQL (which I assume the majority of PHP users are accessing), single quotes, double quotes may be used for strings.

If you try to use the "rule" you posted, using single quotes for identifiers, your MySQL query will return a string:

SELECT "create_date" FROM table1
returns: create_date

SELECT 'create_date' FROM table1
returns: create_date

SELECT create_date FROM table1
returns: 2007-06-22

Your determination to demand that the questioner follow SQL standard can very easily cause the questioner to experience unneeded code breakage and frustration. You might have at least asked what database language the questioner was using before stating the "rule".

Sxooter

Yes, that's the VERY long version of what I just posted.

Sxooter

So, tigomark, did you try echoing the mysql_error if there's an error?

laserlight

If you try to use the "rule" you posted, using single quotes for identifiers, your MySQL query will return a string:

Actually, the rule is to use double quotes for identifiers and single quotes for strings, and that was what Sxooter posted. I would say that since single quotes are always used to delimit strings in SQL, it makes sense to use them over double quotes, even if your database engine accepts double quote delimited strings. tigomark would not "experience unneeded code breakage and frustration", since it was clarified "that MySQL decided to use ` to quote identifiers".

nemonoman

This uninformative code:
$result = @($sql,$connection) or die("Couldn't execute query.");
should have produced a "Coudn't execute query" and death if there was an error. So one can conclude that the query executed error free, and selected 0 rows.

Therefore the next place to look is at the selection query itself, and how it relates to the actual data in the db.

Three likely areas:

unintended typos in the State data fields so no match
State records, but with no other significant data
Since query is a "SELECT * " type, check for mismatchs between columns as named in DB and as referenced in code (i.e., if db column is CITYNAME, $row['CityName'] will be null).

You might test the returned row this way:

while ($row = mysql_fetch_array($result)) {
/* ////////////////////////////////////////// */
/* add printout of the row content */
/* ////////////////////////////////////////// */

print_r($row);

/* ////////////////////////////////////////// */

$id = $row ['id'];
$State = $row ['State'] //...etc

nemonoman

laserlight wrote:
tigomark would not "experience unneeded code breakage and frustration", since it was clarified "that MySQL decided to use ` to quote identifiers".

I wrote my response before Sxooter 'clarified' or "clarified" ( or maybe, clarified) his answer.

Telling someone to use constructs that WILL break the query -- even with later clarification seems to me a doubtful approach. In fact, when someone posts a problem, I'm not sure that it's best practice to begin by pointing out abstract concerns (and then giving doubtful advice that requires later clarifying).

Best practice, it seems to me, is to focus down on the question being raised.

Tigomark appears to be experiencing a data problem: not a language problem, not a single/double quote/backtick problem, not a SQL problem, not a PHP problem. No need to chase along detours.

Sxooter

So, how do you KNOW he's not running mysql 5 in strict ansi-sql mode? Seriously, a lot of new installations have that turned on now.

Most of the examples you gave break on my install of MySQL 5 because I have ansi-sql mode turned on.

But yeah, it does look like he might either have php interpretation turned off, or it's not turned on for the extension he's using in that script.

Sxooter

Oddly enough, quoting with ` still works with ansi mode turned on. Yet another thing that doesn't get ansi-sqlified when you turn that mode on.

nemonoman

Sxooter wrote:
So, how do you KNOW he's not running mysql 5 in strict ansi-sql mode?

Because his query didn't die with an error.