Trying to CONCAT two columns in a Select query...

[Jim R]

I'm trying match data from two different tables.  The single_tag_title is producing the results I want via the echo, but I can't seem to get the syntax down in the query.

 $slug = single_tag_title();
 echo $slug;
 

$query = 'SELECT * FROM a_playerRank WHERE CONCAT(nameFirst," ",nameLast) = "$slug"';

$results = mysql_query($query);

while($line = mysql_fetch_assoc($results)) {

I get the following error:

 

 

 

WARNING: MYSQL_FETCH_ASSOC(): SUPPLIED ARGUMENT IS NOT A VALID MYSQL RESULT RESOURCE IN/HOME4/#####/PUBLIC_HTML/#####/WP-PLAYERPROFILE.PHP ON LINE 14

 

[ginerjm]

might help if we could see line 14

[Barand]

That error usually signals an error in the query. Check value of mysql_error().

 

Also try swapping the " and ' around so $slug is interpreted correctly

[objnoob]

$query = 'SELECT * FROM a_playerRank WHERE CONCAT(nameFirst," ",nameLast) = \'' . $slug . '\'';

or better yet....

$query = "SELECT * FROM a_playerRank WHERE CONCAT_WS(' ', nameFirst, nameLast) = '{$slug}'";

 

might help if we could see line 14

 

Line 14 is....   while($line = mysql_fetch_assoc($results)) {

[ginerjm]

My bad - I should of seen what objnoob pointed out about the error.

 

So - the query failed.  If error checking had been done (  if (!$results)..... ) you should have seen a hint as to what part of the query was invalid.

[Jim R]

@objnoob, 

 

I got the same error using your second line.  

 

 

Maybe more information will help:

In WordPress, I'm trying to match an Tag archive to the first and last name of the player in a separate data table, where I have profile type information.  I had a small plugin I wrote that copied over their tag_id to the separate data table, but a WordPress upgrade broke it.  As I was trying to fix it, I figured I could just use Tag Archive name and match it.

 

So it used to work and work well.

[Riparian]

try this... worth a try (the commas always mess me up.)

 

 

$query = "SELECT * FROM a_playerRank WHERE CONCAT(nameFirst,' ',nameLast) = '$slug' ";

[mac_gyver]

i recommend rereading the first line in post/reply #3.

 

when you have an error, until you determine what is causing it, there's little point in just trying different things that might not have anything to do with the problem.

[Jim R]

Are we not trying to determine what is causing it?  Bad syntax in the query or gathering of results.  

[mac_gyver]

after the first suggested change to the sql statement (second line in post/reply #3), all the different sql statements have been equivalent and if they aren't working, means something else is wrong that is causing the query to fail, such as the connection, selected database, table/column names...

 

the only sure and quick way (you should always have error checking logic in your code, it's not just for debugging when you have a problem) is to test if the query returned a result set or a false value and then use the suggested mysql_error() statement to find out why it is failing.

[Jim R]

It's not the connection, selected database or table/column.  The only time the error arrives is when I add the WHERE condition.

Edited January 31, 2014 by Jim R

[mac_gyver]

i'm going to guess that the $slug variable contains some sql special characters that are breaking the sql syntax.

 

the mysql_error() output will identify what is causing the query to fail and at what point in the query a problem was found.

 

using mysql_error() has been suggested multiple times in this thread to find the cause of the problem. if you are not going to follow the advice given, it's going to take you a very long time to solve this.

[Jim R]

Where should I echo the mysql_error()?

 

 

Here is what it is showing:

 

WARNING: MYSQL_ERROR(): SUPPLIED ARGUMENT IS NOT A VALID MYSQL-LINK RESOURCE IN/HOME4/#####/PUBLIC_HTML/RESOURCES/WP-PLAYERPROFILE.PHP ON LINE 13

 

 

 

Line 13 is

 

echo mysql_error($results);

Edited January 31, 2014 by Jim R

[Ch0cu3r]

Dont pass $results to mysql_error().

echo mysql_error();

[Jim R]

ILLEGAL MIX OF COLLATIONS (UTF8_BIN,NONE) AND (UTF8_GENERAL_CI,COERCIBLE) FOR OPERATION '='
 

Edited January 31, 2014 by Jim R

[Jim R]

As best as I can tell, all my collations match.  I also commented out the line that defines $slug, same error.  

Edited January 31, 2014 by Jim R

[Jim R]

I've tried it a different way:

 $slug = single_tag_title();
 echo $slug;

$query = "SELECT * FROM a_playerRank";

$results = mysql_query($query);
echo mysql_error();
while($line = mysql_fetch_assoc($results)) {

	$nFirst = $line['nameFirst'];
	$nLast = $line['nameLast'];
	$nameFull = "{$nFirst} {$nLast}";

if ($nameFull == $slug)  {

No more error, but it's still not working.  It prints the $slug properly.  When I put in a line to echo $nameFull of the IF statement, it prints all of them properly, meaning first name (space) last name, so at some point is it printing a result for $nameFull that equals $slug.  However, for the IF statement, it's not recognizing it.

[Psycho]

That could be because the two values are not equal. What are the values of $nameFull and $slug that you think should be compared as equal?

 

Try this:

 

while($line = mysql_fetch_assoc($results)) {


	$nFirst = $line['nameFirst'];

	$nLast = $line['nameLast'];

	$nameFull = "{$nFirst} {$nLast}";

	echo "nameFull : $slug

\n"; //Output the values to compare visually

}

 

Even if two values "look" the same they may not be. There can be non-printable characters in one. The best way to verify is to use var_dump() on a value to know exactly what it contains.

[Jim R]

It's basically the Page slug with null at the end.  KJ WALTONNULL  

 

Not sure how it would get that, but that's why I'm here asking questions.  :-)

Edited February 1, 2014 by Jim R

[Psycho]

No, you were here asking why you were getting the error which was due to a bad query. Then you were asking why two values were not comparing. Now, you are asking why there is the text 'NULL' at the end of a value. This is all the result of sloppy coding. Do one thing and verify it works. Then move on to the next thing.

 

You don't even state which value has the 'NULL' at the end: $slug or $nameFull. If it is $slug, then the value is getting put there from the function that you use to define $slug

$slug = single_tag_title();

My crystal ball is at the shop right now getting its divinator replaced so I am unable to tell you what problem exists in that function.

 

If the NULL is at the end of $fullName, then that value is getting put into the database. I have no what processes you are running to put data into the database to tell you why that may be happening

[Jim R]

You said use var_dump() so I did and wrote the results for you to read.  

 

 

var_dump($slug);   produced =>  KJ WALTONNULL      (css is producing the all caps)

 

 

I did do "one thing and verified it works".  I said in post #17 with simpler query, which I provided in the code box, the WHILE produced every name in the database for $nameFull in the format I defined.  That means is produced on the screen KJ WALTON, which should've equaled what the $slug printed, KJ WALTON.  

 

The var_dump($slug) printed KJ WALTONNULL

 

So I'll be more specific:  I don't know why printing $slug would produce a different result than var_dump().  I would assume that if I knew why, I would be able to solve this for myself, but since I can't, I'm here asking questions.

[Psycho]

The var_dump($slug) printed KJ WALTONNULL

No, it did not. It would have produced much more than that. var_dump() will tell you they type of variable it is and, based on the type, additional details such as number of characters.

 

 

So I'll be more specific:  I don't know why printing $slug would produce a different result than var_dump().

 

Because, as I stated earlier, the variable could have non-printable characters included in it. Which is exactly what I think is happening here. So, when you do a simple echo of the variable it doesn't print those characters to the page - such as a NULL value. That is exactly why I suggested you use var_dump() because it will tell you exactly what the variable contains. If there are non-printable characters it can translate those into a human readable form in many cases. So, please post EXACTLY the output from var_dump(). If there is CSS that is modifying it then get the output from the HTML source code. Programming is an EXACT science, we need to know exactly what is happening to fix it. but, regardless of what is in $slug, the value is getting there from the function single_tag_title() which you have not provided any code for.

[Jim R]

var_dump($slug) is producing NULL

 

 

In the code I quoted:  $slug = single_tag_title();

It isn't assigning the value of single_tag_title to $slug.  It's just printing it on the screen.  single_tag_title is produced by WordPress taxonomy.

[Jim R]

I figured it out.  Once I figured out a value was printing the single_tag_title instead of passing a value to $slug, I dug more into the WP template tag.  It acts a little differently than WP hooks I have used in other areas.