sql query with left join in php don't work

Question

I made a SQL query in my database and it works perfectly, but when I try to put this same query into PHP, it doesn't work. I cannot figure out where is the error.

//connection variables
$host = "localhost";
$database = "kjnjkyeo3";
$user = "root";
$pass = "probajovo11";

//connection to the database
$connection = mysql_connect($host, $user, $pass, $database)
or die ('cannot connect to the database: ' . mysql_error());


$sql = "SELECT ps_orders.id_order, ps_order_detail.id_order, ps_order_detail.product_reference AS Itemno,  ps_order_detail.product_quantity AS Saleqty, ROUND(ps_order_detail.total_price_tax_incl, 2) AS Cost, DATE_FORMAT(ps_orders.date_add , \'%Y%m%dT%T\' ) AS Dateoftrans\n"
    . "FROM ps_orders\n"
    . "LEFT JOIN kjnjkyeo3.ps_order_detail ON ps_orders.id_order = ps_order_detail.id_order";

//loop to show all the tables and fields
$loop = mysql_query($sql)
or die ('cannot select tables');

I made a lot of changes in my query but I always get the message cannot select tables. When I make a simple query like Select tables from $database, it works fine.

I made the changes:

//connection variables
$host = "localhost";
$database = "kjnjkyeo3";
$user = "root";
$pass = "probajovo11";

//connection to the database
$connection = mysql_connect($host, $user, $pass)
or die ('cannot connect to the database: ' . mysql_error());

//select the database
mysql_select_db($database)
or die ('cannot select database: ' . mysql_error());

$sql = "SELECT ps_orders.id_order, ps_order_detail.id_order, ps_order_detail.product_reference AS Itemno,  ps_order_detail.product_quantity AS Saleqty, ROUND(ps_order_detail.total_price_tax_incl, 2) AS Cost, DATE_FORMAT(ps_orders.date_add , \'%Y%m%dT%T\' ) AS Dateoftrans FROM ps_orders LEFT JOIN ps_order_detail ON ps_orders.id_order = ps_order_detail.id_order";

//loop to show all the tables and fields
$loop = mysql_query($sql)
or die ('cannot select tables');

but it still doesn't work

i made this query directly in mysql database and i generate the sql statment with the option in database and he puts the \n. — user2989040
– user2989040, Commented Jan 20, 2014 at 8:50
are you sure database is a he? Think about it, it may be she :P — Altaf Hussain
– Altaf Hussain, Commented Jan 20, 2014 at 9:09

Balaji Kandasamy · Accepted Answer · 2014-01-21 06:35:38Z

1

$connection = mysql_connect($host, $user, $pass) or die ('cannot connect to the database: ' . mysql_error());
mysql_select_db($database);
$sql = "SELECT ps_orders.id_order, ps_order_detail.id_order,
 ps_order_detail.product_reference AS Itemno,  ps_order_detail.product_quantity AS Saleqty, ROUND(ps_order_detail.total_price_tax_incl, 2) AS Cost, DATE_FORMAT(ps_orders.date_add , \'%Y%m%dT%T\' ) AS Dateoftrans FROM ps_orders LEFT JOIN kjnjkyeo3.ps_order_detail ON ps_orders.id_order = ps_order_detail.id_order";

edited Jan 21, 2014 at 6:35

answered Jan 20, 2014 at 8:48

Balaji Kandasamy

4,50410 gold badges42 silver badges58 bronze badges

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

Comments

Hanky Panky · Accepted Answer · 2014-01-20 08:47:26Z

0

mysql_connect doesn't allow you to select the database like you are doing. You have to use mysql_select_db and one day you'll also have to eventually move to mysqli or PDO.

$connection = mysql_connect($host, $user, $pass) or die ('cannot connect to the database: ' . mysql_error());
mysql_select_db($database);

answered Jan 20, 2014 at 8:47

Hanky Panky

46.9k9 gold badges76 silver badges95 bronze badges

Comments

Community · Accepted Answer · 2023-11-17 19:36:10Z

0

mysql_connect does not take a $database parameter. https://www.php.net/manual/en/function.mysql-connect.php, instead use:

$connection = mysql_connect($host, $user, $pass)
mysql_select_db($database, $connection)

edited Nov 17, 2023 at 19:36

CommunityBot

11 silver badge

answered Jan 20, 2014 at 8:47

Mick Hansen

2,70419 silver badges14 bronze badges

Comments

Community · Accepted Answer · 2023-11-17 20:14:12Z

0

Use mysql_select_db($database) to tell on which databases will the queries run.

If you are selecting from multiple databases you can use this way of referecing a field

`database_name`.`table_name`.`field_name`

or just

`database_name`.`table_name`

for a table in FROM.

mysql_* functions are deprecated, if you are starting a new project use mysqli or pdo

edited Nov 17, 2023 at 20:14

CommunityBot

11 silver badge

answered Jan 20, 2014 at 8:51

aconrad

5866 silver badges12 bronze badges

Collectives™ on Stack Overflow

sql query with left join in php don't work

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related