Problem passing variable with mysql SELECT
7 posts by 2 authors in: Forums > CMS Builder
Last Post: February 2, 2015 (RSS)
Hi all,
I'm having a problem with the mysql SELECT not passing the variable I'm using. Here's the code:
foreach ($scheduledRecords as $record){
$readr = htmlencode($record['reader']);
$check = mysql_query("SELECT email, phone FROM cms_readers WHERE full_name = '" . mysql_real_escape_string($readr) . "'") or die(mysql_error());
$check2 = mysql_num_rows($check);
// if reader exists get email address
if($check2 > 0){
while($ifo = mysql_fetch_array( $check )){
$r_em = $ifo['email'];
$r_ph = $ifo['phone'];
}
}else{
$r_em = '';
$r_ph = '';
}
}
$readr is the variable that is not accepted and is taken from another table. If I put the actual reader name in, it finds the record ($check2 is > 0). This same code works in other files but no matter how I try to duplicate it, it still will not pass the $readr variable.
Any ideas?
Please help to preserve my sanity!
Thanks in advance,
Eric
By claire - February 2, 2015
Hi Eric
The first thing that I'd suggest is adding a var_dump($readr) to the code just after the variable assignment, then run the code when it doesn't work. You'll need to establish exactly what's being inserted into the SQL statement.
You can also try var_dump(mysql_real_escape_string($readr)) if nothing obvious shows up in the first var_dump, just to see if something is being changed by the escape function.
Claire Ryan
interactivetools.com
Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Hi Claire,
Thanks for replying! I added both var-dumps (separately) and both displayed the same data (the variable $readr name). However, I did notice the length of each string was one more than the actual string; e.g., string(10) "Bill Hull" showed a length of 10 even though it is only 9 characters in length. Using the trim, ltrim or rtrim made no difference. Is there a 'hidden' character when using htmlencode($record['reader']) -- specifically the htmlencode -- when retrieving the field from the table using foreach($scheduledRecords as $record); specifically at the beginning? I'm guessing not because when I just used $record['reader'] it still made no difference.
I've even tried looping through the 'readers' table and when the variable is used it still does not 'see' the contents. BUT when I hardcode the name instead of using the variable, it works every time. Funny thing is, the mysql code is used in other modules and it works fine! If I ONLY use the mysql statement and assign a variable ($readr = 'Bill Hull'), it works so I'm thinking it has something to do with the foreach process when extracting the record from the table using the foreach clause.
Server Info:
PHP Version: PHP v5.3.24
Database Server: MySQL v5.0.96
I appreciate your time and thank you in advance for ANY suggestions.
Eric
By claire - February 2, 2015
Hi Eric
This stuff is always a little tricky to debug. Can you check the source of the page when you do the var_dumps? It's possible that there's an extra character there that doesn't show up in the browser itself.
Is there a reason you need to use htmlencode there?
Claire Ryan
interactivetools.com
Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/
Hi again Claire,
I was using the htmlencode because it's what is generated when I use the "Code Generator" in CMSB. I subsequently have taken out that reference but alas, still no luck. BTW, the 'test' var subjects all have email and phone numbers...
I've attached the test file for you to look at that I've been using to try to narrow down what's happening. Maybe another set of eyes (yours) can see what I'm sure I've been missing.
I forgot to mention in my info that I'm using version 2.63 (Build 1092) of CMSB; not sure that matters.
Again, thanks for your time!
Eric
By claire - February 2, 2015
I get the feeling that there's something else going on here - perhaps some wrong info is being saved to the database?
I think I'd like to get a closer look at this. Can you open a support ticket please? https://www.interactivetools.com/support/email_support_form.php
Claire Ryan
interactivetools.com
Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/