PHP, mySQL . SELECT statement incorrect

Question

My two DB arrays:

ORDER --> (id, customer_id)

CUSTOMER --> (id, name)

They have foreign key(order.customer_id and customer.id).

I have an html form with a textfield and submit button, that user gives the name of the customer and i want to take from a query, the orders of the selected customer. This is my code, I have an error in SELECT statement. If anyone can help...

     <?php

$connect = mysql_connect("localhost", "root", "") or die("Unable to connect to MySQL");

$selected = mysql_select_db("eshop", $connect) or die("Could not select examples");

if (isset($_POST['name'])) {
$customerName = $_POST['name'];
}


$result = mysql_query("SELECT order.id 
                   FROM  order, customer              
                   WHERE order.customer_id = customer.id
                   AND customer.name = $customerName
                   ");          



if($result === FALSE) {
die(mysql_error()); // TODO: better error handling
}
while($row = mysql_fetch_array($result))
{
echo $row['order.id'];

}

 mysql_close($connect);
 ?>

Please, before you write **any** more SQL interfacing code, you must read up on [proper SQL escaping](http://bobby-tables.com/php) to avoid severe [SQL injection bugs](http://bobby-tables.com/). Also, `mysql_query` should not be used in new applications. It's a deprecated interface that's being removed from future versions of PHP. A modern replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/). A guide like [PHP The Right Way](http://www.phptherightway.com/) will help you avoid making mistakes like this. — tadman, Dec 12 '13 at 18:40
[**Read this (before going LIVE)**](http://stackoverflow.com/q/60174/1415724) before someone gets to you. And while you're in a reading mood, [**read this too**](https://www.owasp.org/index.php/Top_10_2013-Top_10) — Funk Forty Niner, Dec 12 '13 at 18:43

Krish R · Accepted Answer · 2013-12-12T19:04:31.097

order is a reserved word, so use backticks to wrap it with. I.e.: `order`

$result = mysql_query("SELECT `order`.id
        FROM  `order`, customer
        WHERE `order`.customer_id = customer.id
        AND customer.name = '$customerName'
        ");

Your Code:

        if (isset($_POST['name'])) {
            $customerName = trim($_POST['name']);

            $result = mysql_query("SELECT `order`.id as OrderId
                    FROM  `order`, customer
                    WHERE `order`.customer_id = customer.id
                    AND customer.name = '$customerName'
                    ") or die(mysql_error());               

            while($row = mysql_fetch_array($result))
            {
                echo $row['OrderId'];

            }               
        }

Note: Use mysqli_* functions or PDO instead of mysql_* functions(deprecated)

Thanks A lot!! I want to ask you:1) you say " SELECT `order`.id " as OrderId, because i will have problem when i" echo $row['OrderId'];" 2)When i save data to DB like a name Alex Alexiou, this named is saved to DB as one word? So you used "trim()"? — alexca, Dec 12 '13 at 19:02
Reason being used trim for to avoid whitespace from inputfiled. — Krish R, Dec 12 '13 at 19:09

score 0 · Answer 2 · answered Dec 12 '13 at 18:45

Bare minimal query change if your end user is super-reliable, (adding the quotes for variable string)

$result = mysql_query("SELECT order.id 
                   FROM  order, customer              
                   WHERE order.customer_id = customer.id
                   AND customer.name = '$customerName'
                   ");

Anyways, escaping is very important.

score 0 · Answer 3 · answered Dec 12 '13 at 18:50

try

$result = mysql_query("SELECT order.id 
                   FROM  order, customer              
                   WHERE order.customer_id = '$customer.id'
                   AND customer.name = '$customerName'
                   ");

and try declaring a variable for the customer id then insert into double quotes

score 0 · Answer 4 · answered Dec 12 '13 at 21:20

0

Thanks for your answers. I am a little dissapointed because my teachers in university use wrong approach for SQL, like the one i have posted. This is for semester coursework. Thanks anyway!

answered Dec 12 '13 at 21:20

alexca

57
7

PHP, mySQL . SELECT statement incorrect

4 Answers4