• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

What is wrong with my query

Hi experts,

I have a query that gets a column from the customer_details table called "model" the rest of the information is taken from the customer table.

It is then displayed in a table.. It shows nothing...

When i echo out the $result it shows Resource ID#5

Any ideas where im going wrong??

Code is attached.
<?php 
error_reporting(E_ALL);
include 'myphp.php';
$val = $_GET['val'];
$sql = "SELECT * 
FROM customer 
    LEFT JOIN customer_details 
        ON customer.customer_name = customer_details.customer_name 
WHERE customer.customer_name = '" . $val . "'";
$result = mysql_query($sql) or die(mysql_error() . " IN $sql");

echo $result;
 
$rs = mysql_query($sql);

while($row = mysql_fetch_array($rs))
{
   echo"<table>";
   echo "<tr><td>Customer:</td><td>".$row['customer_name']."</td></tr>";
   echo "<tr><td>First Name:</td><td>".$row['first_name']."</td></tr>";
   echo "<tr><td>Last Name:</td><td>".$row['last_name']."</td></tr>";
  echo "<tr><td>Mobile Number:</td><td><a href=\"billing.php?val=".$row['mobile_number']."\">".$row['mobile_number']."</a></td></tr>";
  echo "<tr><td>Model of phone:</td><td>".$row['model']."</td></tr>";
   echo"</table>";
}
   ?>

Open in new window

0
NeoAshura
Asked:
NeoAshura
  • 8
  • 7
  • 2
1 Solution
 
svgmucCommented:
You could var_dump($rs) to see what it returned.
Also, check if the query returns anything by running it manually on a mysql client.
0
 
NeoAshuraAuthor Commented:
vardump returns

what i posted above in the terms of...

resource(5, mysql result)

i should of explained i already tried it in MySQL and it returns all the fields i need.

I just dont know why its not outputting it onto the screen. Its Really Strange!
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>"SELECT *
is the problem. your query returns duplicate column names, which must be avoided.
instead of the *, specify all the columns you really need.


>WHERE customer.customer_name = '" . $val . "'";
is another issue, it allows sql injection.
you have to put a safe_guard:

WHERE customer.customer_name = '" . mysql_real_escape_string($val) . "'";

hope this helps
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
svgmucCommented:
Ah, yeah... I mean a var_dump($row), anyway. But I'd consider angelIII's answer the solution. ;)
0
 
NeoAshuraAuthor Commented:
im guessing it would be something like this??

$sql = "SELECT customer_name,first_name,last_name,mobile_number,model,IMEI,
FROM customer
    LEFT JOIN customer_details
        ON customer.customer_name = customer_details.customer_name
WHERE customer.customer_name = '" . mysql_real_escape_string($val) . "'";


???
0
 
NeoAshuraAuthor Commented:
even tho the model and IMEI are from the customer_details table?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have a trailing "," in your query, now (at least in what you posted)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
anyhow, please post the error you get (die(mysql_error())
0
 
NeoAshuraAuthor Commented:
here you go angel thanks again.

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM customer LEFT JOIN customer_details ON customer.customer_nam' at line 2 IN SELECT customer_name,first_name,last_name,mobile_number,model,IMEI, FROM customer LEFT JOIN customer_details ON customer.customer_name = customer_details.customer_name WHERE customer.customer_name = '2745031'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as I wrote: you have a "," between IMEI and FROM. remove that.
0
 
NeoAshuraAuthor Commented:
thanks angel now i have a new error

Column 'customer_name' in field list is ambiguous IN SELECT customer_name,first_name,last_name,mobile_number,model,IMEI FROM customer LEFT JOIN customer_details ON customer.customer_name = customer_details.customer_name WHERE customer.customer_name = '2745031'
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this means that the column can come from both tables, and you must specify from which you want to actually take it.
your join will actually make that it would not matter, but SQL is not "that intelligent"
SELECT customer.customer_name,first_name,last_name,mobile_number,model,IMEI FROM customer LEFT JOIN customer_details ON customer.customer_name = customer_details.customer_name WHERE customer.customer_name = '2745031'

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this said, you should use table aliases, to make your queries readable by other persons.

for example, it could be like this (I have to guess from which table the different columns come from, please correct as needed)
SELECT c.customer_name, c.first_name, c.last_name, cd.mobile_number, cd.model, cd.IMEI 
FROM customer c
LEFT JOIN customer_details cd 
  ON c.customer_name = cd.customer_name 
WHERE c.customer_name = '2745031'

Open in new window

0
 
NeoAshuraAuthor Commented:
Hi Angel, With your query above i now get the following again:

resource(5, mysql result)
resource(6, mysql result)

0
 
NeoAshuraAuthor Commented:
I passed your query through Mysql and it works in php my admin... so im a little confused as to what the problem could be
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
from what line?
echo $result;
will not print the resultset, but indeed only the resourceid information.

the loop you have should do the job.
0
 
NeoAshuraAuthor Commented:
Im an Idiot sorry for wasteing your time needed to change last line to this.

WHERE c.account_number = '" . $val . "'";

and it worked i was look at the wrong column..

sorry thnx again
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 8
  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now