Matching file names with mysql queries / Stripping capital letters...

[Jim R]

In querying my database, I'm Selecting nameFirst and nameLast, and it produces a name like Joe Smith.  I'm trying to match a photo with the name.  Right now I'm uploading photos into a folder naming the file (e.g. SmithJoe.jpg). 

 

For reasons that involve writers being able to upload and access photos, I'm trying to use an image plugin.  When uploading photos, it strips capital letters, so SmithJoe,jpg goes in as smithjoe.jpg, and it's not matching my database query. 

 

Here is the code I'm working with that works quite well with this one exception:

 

$query = 'SELECT * FROM wp_playerRank';
$results = mysql_query($query);
while($line = mysql_fetch_assoc($results)) {

if ($line['wpID'] == $wp_tagID)  {

echo '<div class="player">';


// Here is the code that produces the image.  I need to get rid of capital letters for ease of use

echo '<div><img src="/wp-content/uploads/' . $line['nameLast'] . $line['nameFirst'] . '.jpg"></div>';



echo '<div class="playerData">' . $line['height'] . ' ';
	if ($line['position'] == 'PG')  {echo 'Point Guard';}
	elseif ($line['position'] == 'SG')  {echo 'Shooting Guard';}
	elseif ($line['position'] == 'SF')  {echo 'Small Forward';}
	elseif ($line['position'] == 'PF')  {echo 'Power Forward';}
	elseif ($line['position'] == 'C')  {echo 'Center';}
	echo '</div>';
echo '<div class="playerData">' . $line['hschool'] . '</div>';
echo '<div class="playerData">' . $line['summer'] . '</div>';
echo '<div class="playerData">Class of ' .$line['year'] . '</div>';

	if ($line['committed'] == 'y') {
echo '<div>
<br>Committed to <strong>'. $line['college'] . '</strong></div>
';}

}
}

 

 

[Psycho]

You just need to change the "collation" for the first and last name fields in the database so queries are handled in a case-insensitive manner. The collations will sometimes end with an underscore and a letter or two. You need to use one that has an "i" at the end.

 

For example: latin1_general_ci or utf8_general_ci

 

FYI: I forget the term the "c" represents, it allows the queries to be run against accented characters. So, the letter "a" would match "à" or "ã".

[Jim R]

Will that change how it's used by other code where it needs the capital letters? 

[Psycho]

It doesn't change the values. It gives MySQL a "Set of rules for comparing characters in a character set": http://www.sitebuddy.com/mssql_info/mysql_character_sets_and_collation_explained

 

Do you have a need to do a query such as "WHERE name LIKE '%A%'" and ONLY find records where the name has a capital letter - excluding records with lower case letter "a"? If not, then change the collation to one that include the insensitive flag (i.e. the letter "i" at the end). You could always change the collation back if you find a problem. That what testing is for.

[Jim R]

The code I put here is actually the entirety of my need on this issue.  I'm using WordPress, and I'm matching WordPress slugs to records in my custom data table.  That has to be case sensitive (or has been my experience), but when I upload images via one of the plugins, it strips the capital letters.

 

I'd like for my other writers to be able to access photos more easily, so that plugin really helps.

[Jim R]

I'm using latin1_swedish_ci, BTW.  Not sure why. 

[Psycho]

I have no idea what Word Press is doing, but if some process is change capitals to lower case I highly doubt it has anything to do with the database. So, if the photo name is stored as "smithjoe.jpg" in the database you can query it using "SmithJoe.jpg" or "SMITHJOE.JPG" or any combination of upper/lowercase letters.

 

However, if your problem is that you are querying the db and getting back "JoeSmith.jpg" and you are having a problem using that value to create an image tag, then just convert the values to lowercase using strtolower().

 

If neither of those is your problem, then I'm not understanding the problem. Have you TRIED changing the collation? Like I said, you can change it back if it doesn't work as expected.

 

Edit: try a colation such as latin or utf8, the swedish one might be causing some of the problems.

[Jim R]

I tried changing it to latin1_general_ci because you had referred to that setting, but it gave me an error. 

 

How and where (db or query) do I convert from upper to lower using strtolower()?

[Jim R]

Ok...just changed it to latin1_general_ci, and I didn't get an error.  I may have tried a different setting.

[Jim R]

After changing the collation, I changed my code to echo what the image plugin produces, and the image doesn't show.  I just need my code to get nameLast = Smith, nameFirst = Joe, and echo it as smithjoe.

 

 

[Psycho]

Seriously, all you need to do is convert a string to lower case? You really made this seem a much more difficult problem. Just use strtolower().

 

$fname = "Joe";
$lname = "Smith"
$fullName = "{$lastName},{$firstName}";
echo $fullName;
//Output: Smith,Joe
echo strtolower($fullName);
//Output: smith,joe

[Jim R]

I need to change the variables to all lowercase $line['nameLast'] and $line['nameFirst'].  I found what you were referring to, but it doesn't show how to handle multiple column queries. 

 

I tried this, but it didn't work:

 

$nf = $line['nameFirst'];
$nl = $line['nameLast'];
$str = strtolower($nl,$nf);

echo '<div><img src="/wp-content/gallery/head_shots/' . $str . '.jpg"></div>';

[Jim R]

You're a good man.  That pretty much worked:

 

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

echo '<div><img src="/wp-content/gallery/head_shots/' . strtolower($nameFull) . '.jpg"></div>';

 

 

[Psycho]

A bit simpler:

$nameFull = strtolower($line['nameFirst']).strtolower($line['nameLast']);
echo '<div><img src="/wp-content/gallery/head_shots/{$nameFull}.jpg"></div>';