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
Solved

What is wrong with my query

Posted on 2011-03-22
17
244 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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
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.

829 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