Page 1 of 1
Use of variable in value portion of where clause.
Posted: Wed Jan 05, 2011 11:56 am
by joecz
Hi
[text]I am trying to find out the correct syntax to use when I want to use a variable as the value in the "Where" portion of the select clause
All the examples of a where clause that I have checked out have not used a variable as the value.
In the code below the "Select "statement in green works.
When I try to use a variable which has the sme content "Some Company' as in the line in red it doesn't work.
I am very new to PHP , only a few weeks.
Some background
I have a form on screen 1 which has a select drop down box which shows the company name in every record of the database. The company field has the primary key so no company record can be duplicated.
Once the user selects a company and presses the submit button this code is activated and the variable "$selco" is created and the company name selkected is put into the variable using the $_POST(company) variable.
The plan is to get all the rest of the information on the company record onto a form so the user can update the information .
I still have a lot of work to do to create the for and put the values on the form but the echo statements are just the m,eans IO am using to see if the date is being extracted.
Thanks
[/text]
Code: Select all
<?php
//This echo is just to c onfirm during development that
//the company name selected in a previous form is carried as variable. It does
echo $_POST[company];
$selco = $_POST[company];
echo $selco;
include ("conninc.php");
//This select statement works extracting the correct record from the database.
[color=#40BF00]$query1 = "SELECT * FROM companies WHERE company = 'Some Company'";[/color]
//This select statement doesn't work . It uses a variable whose value is the same
//as the string in the other select statement
[color=#FF0000]//$query1 = "SELECT * FROM companies WHERE company = '$selco' ";[/color]
$results1 = mysql_query($query1 , $link) or die (mysql_error());
while ($row1 = mysql_fetch_array($results1)) {
extract ($row1);
}
echo $addressln1;
echo $addressln2;
echo $addressln3;
echo $addressln4;
?>
Re: Use of variable in value portion of where clause.
Posted: Wed Jan 05, 2011 12:05 pm
by Jade
Try this:
Code: Select all
<?php
echo $_POST['company'];
$selco = $_POST['company'];
include ("conninc.php");
$query1 = "SELECT * FROM companies WHERE company = '$selco' ";
$results1 = mysql_query($query1 , $link) or die (mysql_error());
while ($row1 = mysql_fetch_array($results1)) {
extract ($row1);
echo $addressln1;
echo $addressln2;
echo $addressln3;
echo $addressln4;
}
?>
Re: Use of variable in value portion of where clause.
Posted: Wed Jan 05, 2011 12:29 pm
by joecz
[text]Thanks Jade but that didn't make any difference
I am expecting this output
Some Company Some Company 1234 Some StreetApt. 34Nowhere ON N6Y 7I9
And this is what I get when I don't use the variable
When I use the variable i get this output
Some Company Some Company
which is simply the echo statements of the variables.
I got the same output with your suggested changes.
Thanks again.
[/text]
Re: Use of variable in value portion of where clause.
Posted: Wed Jan 05, 2011 2:02 pm
by Jade
Are you getting database errors? Do you have a field named addressln1? Instead of extracting $row1 try doing a print_r($row1) inside of your loop and make sure you're getting results back from your query.
Re: Use of variable in value portion of where clause.
Posted: Wed Jan 05, 2011 3:00 pm
by joecz
[text]HI Jade
I am not getting any errors at all. The fields I am using in the echo addressln1 etc are all there in the table.
I am positive the select statement is not selecting anything and it is I believe the syntax I am using for the variuable that is the cause.
When i do the select using a string which is the value of the variable everything works fine.
Here is what the print-r gave me when I used the string
Some Company Some Company Array ( [0] => Some Company [company] => Some Company [1] => [2010sponsorship] => [2] => 20101229165226 [id] => 20101229165226 [3] => 1234 Some Street [addressln1] => 1234 Some Street [4] => Apt. 34 [addressln2] => Apt. 34 [5] => Nowhere ON [addressln3] => Nowhere ON [6] => N6Y 7I9 [addressln4] => N6Y 7I9 [7] => 519-205-2935 [phone] => 519-205-2935 [8] => 519-555-6699 [fax] => 519-555-6699 [9] =>
jd@czernai.ca [email] =>
jd@czernai.ca [10] => nice company [Notes] => nice company )
but only this when I usede the variable
Some Company Some Company
And I am sure the ontents of the variable are being seen by the select statement because if I use == instead of = an error message comes up saying the syntax is incoorrect near "Some Company" which is hte value of the variable not the variable.
Thanks again
[/text]
Re: Use of variable in value portion of where clause.
Posted: Wed Jan 05, 2011 3:51 pm
by joecz
[text]Hi Jade
I went on the mysql forum and then to a reference manual and found a local variable can be used but from what I read an understand (remember I'm new to this) it has to be declared by a mysql statement. The url is[/text]
http://dev.mysql.com/doc/refman/5.1/en/ ... iable.html
[text]I'm thinking that I should be able to save the contents of the POST variable to a table in the database and then just use it in a join to find the record I want.. Any thoughts//
Thanks [/text]
Re: Use of variable in value portion of where clause.
Posted: Wed Jan 05, 2011 4:02 pm
by Jade
Have you tried running the query in MySQL without the PHP? What displays to the screen when you run this query:
Code: Select all
<?php
echo $_POST['company'] . " is the company value being posted<br/><br/>";
$selco = $_POST['company'];
include ("conninc.php");
$query1 = "SELECT * FROM companies WHERE company = '$selco' ";
$results1 = mysql_query($query1 , $link) or die (mysql_error());
while ($row1 = mysql_fetch_array($results1)) {
print_r($row1);
}
?>
Re: Use of variable in value portion of where clause.
Posted: Thu Jan 06, 2011 7:29 am
by joecz
[text]HI Shane
Yes the company name is being posted. The first two items on the output line are the result of the two echo statements for the $_POST variable and the $selco variable.
By trying in MYSQL do ypu mean using PHPMYADMIN? Yes I did and I get the result using the string Some Company. I wouldn't know yet how to use a variable created in PHP locally on the server.
If you men something else please explain.
The only thing that displayhs when I run this query using the variable is the result of the two echo statements.
When I run it with the word s "Some Company I get the four address fields printed after the results of the two eco statements of the variables.
Thanks
[/text]
Re: Use of variable in value portion of where clause.
Posted: Thu Jan 06, 2011 10:33 am
by Jade
I'm not exactly sure what you're doing but I believe the problem is that you're not actually POSTing values to the form. If you don't POST the company name via an html <form> then $_POST['company'] will never have a value and your query will never return any results. I believe this is what you're trying to do:
Code: Select all
<?php
//if they've pressed the submit button find companies with a name similar to the one they've entered
if ($_POST['submit'])
{
include ("conninc.php");
$selco = mysql_real_escape_string($_POST['company']);
echo "Search Results For " . $_POST['company'] . ":<br/>";
$loop = mysql_query("SELECT * FROM companies WHERE company LIKE '%$selco%'", $link)
or die (mysql_error());
while ($row = mysql_fetch_array($loop))
{
echo "<b>" . $row['company'] . "</b><br/>" .
$row['addressln1'] . "<br/>" .
$row['addressln2'] . "<br/>" .
$row['addressln3'] . "<br/>" .
$row['addressln4'] . "<br/><br/>";
}
}
//but first you have to post the value of company to PHP using the form POST and an input box with the name company
?>
<form action="#" method="POST">
Enter a company to search for:
<input type="text" name="company" value="<?php echo $_POST['company']; ?>" />
<input type="submit" name="submit" value="Search" />
</form>
Re: Use of variable in value portion of where clause.
Posted: Thu Jan 06, 2011 12:14 pm
by spedula
Try inserting the variable into the select query like this:
Code: Select all
("SELECT whatever FROM table WHERE key='".$value."'")
Re: Use of variable in value portion of where clause.
Posted: Sun Jan 09, 2011 5:25 pm
by joecz
HI Jade and spedula
Thanks for all your help. THe syntax spedula gave me worked.
Joe