MySql / PHP Query Question 2

[GiffordS]

I have a query that uses a php variable, and I am having a bit of trouble fetching the result. I'll create a textbook example.

Example:

$variable=the name of a column ie name, address, etc.

$query="select '$variable', count(*) from table, sort by '$variable' asc";
$r=mysql_query($query) or die(mysql_error());
while($w=mysql_fetch_array($r, MYSQL_ASSOC))
{$num=$w["count(*)"];

Ok, that line is just fine. It returns the correct number of instances of the $variable in the table with each iteration. The problem comes when I try to retrieve the value of the given $variable column.

$val=$w[$variable];

that one just retrieves the column name. It just throws out, for example, "name" each time through the loop.

$val=$w["$variable"];

Does the same thing.

How can I make that statement work?

 

[sleipnir214]

Use an SQL alias.

Change your query to read something like:

"select '$variable', count(*) as foo from table, sort by '$variable' asc";

You can then reference the value as w['foo'].

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[GiffordS]

It's not coming up that way for me, sleip. When I use an alias it still just returns the column label, rather than the individual row values for that column. I agree that this should work. I've been racking my brains all day to figure out why it won't. It's starting to make me a little daffy.

For the record, "select '$variable', count(*) as foo etc." returns nothing for the value of 'foo'.

"select '$variable' as foo, count(*) etc..
returns the column label.

I'm really at a loss here. When I hardcode the query and run the query it works perfectly. Any other thoughts?

 

[GiffordS]

Ok, this just keeps getting more frustrating. I even tried:

$query="select '$variable', count(*) from table, sort by '$variable' asc";
$r=mysql_query($query) or die(mysql_error());
while($w=mysql_fetch_row($r)
{$num=$w[0]";
$val=$w[1];}

and I still get the same result. Let's say that I have a table of personal info and am looking to count how many of each name I have. The result should return something like Bob 3, Ted 1, Alice 2. Instead, I just get name 3, name 1, name 2. Assuming, of course, that $variable="name". I'm really at a loss as to how this is happening. If I hardcode the query as
"select name, count(*) etc. it works beautifly.

 

[herman75]

Your query is wrong.

If I'm right you try to get for example the name and how often it occurs.

You have 2 errors.
1. You have the $variable between ', which means MySQL interprets as a string instead of a fieldname. If you want to quote your fieldnames use `

2. You get your count proper you need to group on the $variable

A proper query would be

$query = "SELECT $variable, COUNT(*) FROM table GROUP BY $variable SORT BY $variable ASC";

 

[GiffordS]

Herman,

The in order to include a variable in an SQL statement you must use the single quote. At least that's how I learned it. Otherwise, the db will look for a column with the literal name of $variable. Remember, PHP evaluates before anything else. By using the single quote PHP returns the value of $valuable to the query string before submitting the query. I may be wrong on that point, but I would be surprised. I know that using just $variable yields nothing, as there is no column on the table named, literally, $variable. As for the rest of your query, that is exactly as I have it written. I left off the group by in this post as an oversight. In any event, I came up with a work around so it's not a high urgency issue. I really am curious, though, as to how to make this work. Anyone else?

 

[sleipnir214]

GiffordS:
Singlequotes are used to delimit string literals in queries. If your column is non-string (say, an integer), you will take a performance hit on your query because MySQL will have to convert the value.


Remember that you aren't passing any variables to MySQL. You are performing a string concatenation within the statement that assigns the query string to the variable $query.


Also:

SELECT 'three' from tablename

is a legal query. It just only returns the string literal three because you haven't told MySQL to return a columnname, you've told MySQL to return a string, because of the quotes.


Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[herman75]

GiffordS,

Did you try the query I provided?

Using the query I provided the following happens

php finds the line and since I have used double quotes (&quot it will search the string for any php-variables and replace them with their php value.

After that you end up with a query

SELECT name, COUNT(*) FROM table GROUP BY name SORT BY name ASC

This query is then send to MySQL who will then return what you requested. Like sleipnir said you can query like this

SELECT 'name' FROM table

but you will just get the string-value 'name' and not the value from the field in the rows of the table.

If you need to quote your columnnames cause you have given them the name of reserved words for example, use backticks (`) as this does work.

 

[sleipnir214]

GiffordS:
Aliasing return columns is the solution to getting your counts out of the query return.

Here's an example....

I have a quotes database, which consists of two tables, quotes_authors and quotes. The relationship between quotes_authors to quotes is one-to-many.

Here is a PHP script which issues a query to MySQL that[ul][li]concatenates the various parts of an author's name into a derived return column aliased as author_name and[/li][li]a count of each author's quotes as a derived return column aliased as quote_count[/li][/ul]for all authors whose lastnames begin with "F". The script then outputs author_name and quote_count into a simple table. The query actually uses the derived column alias name author_name in the GROUP BY clause.

<?php

mysql_connect ('localhost', 'quotes', 'quotes');
mysql_select_db('quotes');

$query = 
&quot;SELECT
	CONCAT(
		IF(LENGTH(qa.author_prefix) > 0, CONCAT(qa.author_prefix, ' '), ''),
		IF(LENGTH(qa.author_fname)  > 0, CONCAT(qa.author_fname,  ' '), ''),
		IF(LENGTH(qa.author_mname1) > 0, CONCAT(qa.author_mname1, ' '), ''),
		IF(LENGTH(qa.author_mname2) > 0, CONCAT(qa.author_mname2, ' '), ''),
		IF(LENGTH(qa.author_lname)  > 0, CONCAT(qa.author_lname,  ' '), ''),
		IF(LENGTH(qa.author_suffix) > 0, CONCAT(qa.author_suffix, ' '), '')
	) AS author_name,
	COUNT(q.id) AS quote_count
FROM
		quotes_authors qa
	JOIN
		quotes q
	ON
		qa.id = q.author_id
WHERE
	qa.author_lname LIKE 'F%'
GROUP BY
	author_name
ORDER BY
	qa.author_lname, qa.author_fname, qa.author_mname1&quot;;
	

$rh = mysql_query ($query);

print '<html><body><table>
';

while ($quote_data = mysql_fetch_assoc ($rh))
{
	print '<tr><td>' . $quote_data['author_name'] . '</td><td>' . $quote_data['quote_count'] . '</td></tr>
';
}

print '</table></body></html>';
?>

Note: Since I am returning values from columns, I am not putting single quotes around column names -- otherwise MySQL would return strings.

Note: Since I am not using MySQL reserved words as column names (never, ever, ever, ever, ever a good idea, in any query or programming language), I do not have backticks around my column names. A lot of GUI-based MySQL management apps put backticks around all column names in queries, on the off chance that the GUI app is unaware of a reserved word that a user has used as a column name.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[GiffordS]

Herman & Sleipnir,

Thanks so much for the help. I don't know why I couldn't get my mind around this the first time Herman explained it. The second explanation made sense. The query did work properly. Thanks again.