Use of variable in value portion of where clause.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
joecz
Forum Newbie
Posts: 6
Joined: Wed Jan 05, 2011 7:43 am

Use of variable in value portion of where clause.

Post 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;

?>

User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Use of variable in value portion of where clause.

Post 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;
}
?>
joecz
Forum Newbie
Posts: 6
Joined: Wed Jan 05, 2011 7:43 am

Re: Use of variable in value portion of where clause.

Post 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]
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Use of variable in value portion of where clause.

Post 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.
joecz
Forum Newbie
Posts: 6
Joined: Wed Jan 05, 2011 7:43 am

Re: Use of variable in value portion of where clause.

Post 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]
joecz
Forum Newbie
Posts: 6
Joined: Wed Jan 05, 2011 7:43 am

Re: Use of variable in value portion of where clause.

Post 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]
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Use of variable in value portion of where clause.

Post 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);
}
?>
joecz
Forum Newbie
Posts: 6
Joined: Wed Jan 05, 2011 7:43 am

Re: Use of variable in value portion of where clause.

Post 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]
User avatar
Jade
Forum Regular
Posts: 908
Joined: Sun Dec 29, 2002 5:40 pm
Location: VA

Re: Use of variable in value portion of where clause.

Post 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>
User avatar
spedula
Forum Commoner
Posts: 81
Joined: Mon Mar 29, 2010 5:24 pm

Re: Use of variable in value portion of where clause.

Post by spedula »

Try inserting the variable into the select query like this:

Code: Select all

("SELECT whatever FROM table WHERE key='".$value."'")
joecz
Forum Newbie
Posts: 6
Joined: Wed Jan 05, 2011 7:43 am

Re: Use of variable in value portion of where clause.

Post by joecz »

HI Jade and spedula
Thanks for all your help. THe syntax spedula gave me worked.
Joe
Post Reply