Solved

What is wrong with my query

Posted on 2011-03-22
17
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 2
17 Comments
 
LVL 14

Expert Comment

by:svgmuc
ID: 35188912
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
ID: 35188980
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35189030
>"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
Why Off-Site Backups Are The Only Way To Go

You are probably backing up your data—but how and where? Ransomware is on the rise and there are variants that specifically target backups. Read on to discover why off-site is the way to go.

 
LVL 14

Expert Comment

by:svgmuc
ID: 35189070
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
ID: 35189332
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
ID: 35189339
even tho the model and IMEI are from the customer_details table?
0
 
LVL 143

Expert Comment

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

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35189356
anyhow, please post the error you get (die(mysql_error())
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35189406
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 143

Expert Comment

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

Author Comment

by:NeoAshura
ID: 35189592
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35189605
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35189620
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
ID: 35189643
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
ID: 35189663
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35189672
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
ID: 35189690
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

705 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