Jim R Posted January 30, 2014 Share Posted January 30, 2014 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 Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/ Share on other sites More sharing options...
ginerjm Posted January 30, 2014 Share Posted January 30, 2014 might help if we could see line 14 Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467143 Share on other sites More sharing options...
Barand Posted January 30, 2014 Share Posted January 30, 2014 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 Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467148 Share on other sites More sharing options...
objnoob Posted January 31, 2014 Share Posted January 31, 2014 $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)) { Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467149 Share on other sites More sharing options...
ginerjm Posted January 31, 2014 Share Posted January 31, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467151 Share on other sites More sharing options...
Jim R Posted January 31, 2014 Author Share Posted January 31, 2014 @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. Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467159 Share on other sites More sharing options...
Riparian Posted January 31, 2014 Share Posted January 31, 2014 try this... worth a try (the commas always mess me up.) $query = "SELECT * FROM a_playerRank WHERE CONCAT(nameFirst,' ',nameLast) = '$slug' "; Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467178 Share on other sites More sharing options...
mac_gyver Posted January 31, 2014 Share Posted January 31, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467180 Share on other sites More sharing options...
Jim R Posted January 31, 2014 Author Share Posted January 31, 2014 Are we not trying to determine what is causing it? Bad syntax in the query or gathering of results. Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467200 Share on other sites More sharing options...
mac_gyver Posted January 31, 2014 Share Posted January 31, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467224 Share on other sites More sharing options...
Jim R Posted January 31, 2014 Author Share Posted January 31, 2014 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467226 Share on other sites More sharing options...
mac_gyver Posted January 31, 2014 Share Posted January 31, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467229 Share on other sites More sharing options...
Jim R Posted January 31, 2014 Author Share Posted January 31, 2014 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467230 Share on other sites More sharing options...
Ch0cu3r Posted January 31, 2014 Share Posted January 31, 2014 Dont pass $results to mysql_error(). echo mysql_error(); Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467233 Share on other sites More sharing options...
Jim R Posted January 31, 2014 Author Share Posted January 31, 2014 (edited) ILLEGAL MIX OF COLLATIONS (UTF8_BIN,NONE) AND (UTF8_GENERAL_CI,COERCIBLE) FOR OPERATION '=' Edited January 31, 2014 by Jim R Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467235 Share on other sites More sharing options...
Jim R Posted January 31, 2014 Author Share Posted January 31, 2014 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467239 Share on other sites More sharing options...
Jim R Posted January 31, 2014 Author Share Posted January 31, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467277 Share on other sites More sharing options...
Psycho Posted January 31, 2014 Share Posted January 31, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467280 Share on other sites More sharing options...
Jim R Posted February 1, 2014 Author Share Posted February 1, 2014 (edited) 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 Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467315 Share on other sites More sharing options...
Psycho Posted February 1, 2014 Share Posted February 1, 2014 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 Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467346 Share on other sites More sharing options...
Jim R Posted February 1, 2014 Author Share Posted February 1, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467350 Share on other sites More sharing options...
Psycho Posted February 1, 2014 Share Posted February 1, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467393 Share on other sites More sharing options...
Jim R Posted February 2, 2014 Author Share Posted February 2, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467420 Share on other sites More sharing options...
Solution Jim R Posted February 2, 2014 Author Solution Share Posted February 2, 2014 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. Quote Link to comment https://forums.phpfreaks.com/topic/285808-trying-to-concat-two-columns-in-a-select-query/#findComment-1467425 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.