Solved

What is wrong with my query

Posted on 2011-03-22
17
246 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
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…

740 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