Query works when SELECT * columns but not when specifying column names

Question

I have a annoying issue, I have a query that uses inner joins, the query prints out the results if i say for example SELECT *, but the moment i start to insert the actual column names it returns my failed query error, of " 0 results".

And i get this error

Notice: Trying to get property of non-object .

SELECT COLUMN NAMES

  $ord_dets = "
  SELECT cod.Fname
       , cod.Lname
       , od.City_name
       , od.Product_Name 
    FROM Customer_order_details cod
    JOIN ord_dets od
      ON od.Order_ID = cod.Order_ID
    JOIN Payment p
      ON p.Order_ID = cod.Order_ID
   WHERE cod.Order_Id = '$OrdID';
   ";

$ord_result = $dbc->query($ord_dets);

if ($ord_result->num_rows > 0) {
while ($row_dets = $ord_result->fetch_array()) {
        $Cust_Fname = $row_dets['Fname'];
}

} else {
 echo "0 results";
  }
$dbc->close();

SELECT *

 $ord_dets = "
 SELECT * 
   FROM Customer_order_details cod
   JOIN ord_dets od
     ON od.Order_ID = cod.Order_ID 
   JOIN Payment p
     ON p.Order_ID = cod.Order_ID
  WHERE cod.Order_Id = '$OrdID';
  ";

$ord_result = $dbc->query($ord_dets);

......

I do not want to select * as i will be selecting data from maybe 4/5 tables.

I have tried fetch_assoc and fetch_row instead of fetch_array and i am getting the same error message.

Can anyone point out what may be causing this issue?

Table schema

ord_dets

 CREATE TABLE `ord_dets` (
 `ord_dets_id` bigint(255) NOT NULL AUTO_INCREMENT,
 `Order_ID` bigint(255) NOT NULL,
 `custmer_ip` varchar(100) NOT NULL,
 `Resturant_ID` bigint(255) NOT NULL,
 `Resturant_name` varchar(100) NOT NULL,
 `City_name` varchar(100) NOT NULL,
 `Product_Id` bigint(255) NOT NULL,
 `Product_Name` varchar(100) NOT NULL,
 `Product_Price` decimal(4,2) NOT NULL,
 `Date_Time_Placed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE   CURRENT_TIMESTAMP,
  PRIMARY KEY (`ord_dets_id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8

Customer_order_details

 CREATE TABLE `Customer_order_details` (
  `Order_ID` bigint(255) NOT NULL AUTO_INCREMENT,
  `Customer_ID` bigint(255) DEFAULT NULL,
  `Fname` varchar(100) NOT NULL,
  `Lname` varchar(100) NOT NULL,
  `Cust_Email` varchar(320) NOT NULL,
  `Cust_M_Phone` varchar(12) NOT NULL,
  `Cust_H_Phone` varchar(16) NOT NULL,
  `Door_num` varchar(100) NOT NULL,
  `Line_1` varchar(200) NOT NULL,
  `Line_2` text NOT NULL,
  `City_Name` text NOT NULL,
  `Postcode` varchar(8) NOT NULL,
  `Status` tinyint(4) NOT NULL,
   PRIMARY KEY (`Order_ID`)
   ) ENGINE=InnoDB AUTO_INCREMENT=147 DEFAULT CHARSET=outfit

My php is weak, but have you tried `$Cust_Fname = $row_dets['Customer_order_details.Fname'];`, or `SELECT Customer_order_details.Fname AS Fname...` — Uueerdo, Jun 21 '16 at 22:23
Based on the information here, the first thing I would try is double-checking for misspelled column names. — Don't Panic, Jun 21 '16 at 22:26
Seems like the query is failing when you specify the names, so the result is a boolean false instead of an object. Run the query in phpMyAdmin, do some basic troubleshooting. If not, provide your table schemas. — Qirel, Jun 21 '16 at 22:28
Your current code does not check for errors in your query, and it seems apparent there are some (after adding column names). You can try some of the steps [here](http://stackoverflow.com/questions/22662488/how-to-get-mysqli-error-in-different-environments) to get some helpful error messages. — Don't Panic, Jun 21 '16 at 22:33
@Don'tPanic thank you. I have error handlers right at the top of my page, the same exact ones mentioned in that post. I have also tripled checked the column names and they are spelt correctly — user6456767, Jun 21 '16 at 22:52
@Uueerdo thank you, no i haven't. I will look into your suggestion and see if that resolves the issue — user6456767, Jun 21 '16 at 22:53
Also, to rule out error in query itself, have you tried to run the query through MySQL Workbench, PHPMyAdmin, or anything? — Uueerdo, Jun 21 '16 at 22:55
@Qirel i have added the schemas, i will run the query in my phpmyadmin as suggested as see the outcome — user6456767, Jun 21 '16 at 22:59
@Uueerdo i have just run the query in myphpadmin and it works — user6456767, Jun 21 '16 at 23:01
**WARNING**: . Whenever possible use **prepared statements**. These are quite straightforward to do in [`mysqli`](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [PDO](http://php.net/manual/en/pdo.prepared-statements.php) where any user-supplied data is specified with a `?` or `:name` indicator that’s later populated using `bind_param` or `execute` depending on which one you’re using. — tadman, Jun 21 '16 at 23:15
As an aside, what does `ord_dets_id` do, and how is it different from `Order_ID` ?? — Strawberry, Jun 21 '16 at 23:34

Query works when SELECT * columns but not when specifying column names

0 Answers0