Solved

What is wrong with my query

Posted on 2011-03-22
17
239 Views
Last Modified: 2012-08-13
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
Comment
Question by:NeoAshura
  • 8
  • 7
  • 2
17 Comments
 
LVL 14

Expert Comment

by:svgmuc
Comment Utility
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
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>"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
 
LVL 14

Expert Comment

by:svgmuc
Comment Utility
Ah, yeah... I mean a var_dump($row), anyway. But I'd consider angelIII's answer the solution. ;)
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
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
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
even tho the model and IMEI are from the customer_details table?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
you have a trailing "," in your query, now (at least in what you posted)
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
anyhow, please post the error you get (die(mysql_error())
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
as I wrote: you have a "," between IMEI and FROM. remove that.
0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
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
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
Hi Angel, With your query above i now get the following again:

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

0
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 6

Author Comment

by:NeoAshura
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now