Session variable is not working in MySQL statement

Question

I am trying to use session variable($_SESSION['asc_id'], which holds some value like "AS0027001") in an SQL statement, but it is not working. When I hardcode the value, it is providing results.

Can anyone please correct me.

MySQL query which is not working

$asc_id = $_SESSION['asc_id'];

$rs = mysql_query('select asc_lastname, asc_firstname, asc_middlename, lname_fname_dob
                     from issio_asc_workers where asc_user_type = 31
                      and asc_id  = "$asc_id"
                      and lname_fname_dob like "' .
                      mysql_real_escape_string($_REQUEST['term']) .
                      '%"  order by lname_fname_dob asc limit 0,10', $dblink);

Mysql query which is working

$rs = mysql_query('select asc_lastname, asc_firstname, asc_middlename, lname_fname_dob 
                     from issio_asc_workers where asc_user_type = 31
                      and asc_id  = "AS0027001" and lname_fname_dob like "' .
                      mysql_real_escape_string($_REQUEST['term']) .
                      '%"  order by lname_fname_dob asc limit 0,10', $dblink);

session variable($_SESSION['asc_id'],which holds some value like "AS0027001") --- why do you think it does? — zerkms
– zerkms, Commented Jan 31, 2012 at 22:30

Joachim Isaksson · Accepted Answer · 2012-01-31 22:31:07Z

3

Variable substitution only works within double quoted strings, not single quoted ones. In other words, you should do;

$rs = mysql_query("select .... and asc_id  = '$asc_id' and ... limit 0,10", $dblink);

Btw, you did make sure the value doesn't include any characters that may lead to SQL injection, right? Otherwise you should use mysql_real_escape_string to make sure before inserting it into a query.

answered Jan 31, 2012 at 22:31

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Devswa Over a year ago

I changed my query like this but its not working. $rs = mysql_query("select asc_pa_lastname, asc_pa_firstname, asc_pa_dob,lname_fname_dob from issio_asc_patient where asc_id = 'mysql_real_escape_string($ascid)' and lname_fname_dob like '". mysql_real_escape_string($_REQUEST['term']) ."%' order by lname_fname_dob asc limit 0,10", $dblink);

Joachim Isaksson Over a year ago

@Devswa You need to add the first mysql_real_escape_string like you correctly added the second; try $rs = mysql_query("select asc_pa_lastname, asc_pa_firstname, asc_pa_dob,lname_fname_dob from issio_asc_patient where asc_id = '".mysql_real_escape_string($asc_id)."' and lname_fname_dob like '". mysql_real_escape_string($_REQUEST['term']) ."%' order by lname_fname_dob asc limit 0,10", $dblink);

Jonathan Leffler · Accepted Answer · 2012-01-31 22:33:42Z

1

When you print the strings, it will be clear. When the question is reformatted to leave the SQL readable, the problem is clear. (The first rule for debugging SQL statements is "print the string". A second rule, that makes it easier to comply with the first, is always put the SQL statements into a string which you pass to the SQL function.)

You use the . notation to embed the request term in the string; you don't use that to embed the $asc_id into the string. You should also use mysql_real_escape_string() on the session ID value to prevent SQL injection.

answered Jan 31, 2012 at 22:33

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

Comments

Kiran · Accepted Answer · 2012-02-01 04:54:01Z

1

First print the variable $asc_id . If it displays nothing, session is unavailable . In that case you missed session_start() in top of the current executing page .

From the SQL query, you cannot replace the value of a variable inside single quoted string . Use . symbol for mixing string value with variable or use double quoted string . I prefer first one .

For troubleshooting , simplest method is printing variable values. From the result , you will understand what is missing .

Thanks

answered Feb 1, 2012 at 4:54

Kiran

9216 silver badges9 bronze badges

Comments

dejjub-AIS · Accepted Answer · 2012-02-01 04:59:57Z

1

Try this. from the comment you added, I modified it like this

session_start(); //add this if you did not do it yet

$asc_id = $_SESSION['asc_id'];

$rs = mysql_query("select asc_lastname, asc_firstname, asc_middlename, lname_fname_dob
                 from issio_asc_workers where asc_user_type = 31
                  and asc_id  = '$asc_id'
                  and lname_fname_dob like '".
                  mysql_real_escape_string($_REQUEST['term']) .
                  "%'  order by lname_fname_dob asc limit 0,10", $dblink);

answered Feb 1, 2012 at 4:59

dejjub-AIS

1,5413 gold badges25 silver badges50 bronze badges

Collectives™ on Stack Overflow

Session variable is not working in MySQL statement

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related