We help IT Professionals succeed at work.

What is wrong with my query?

NeoAshura
NeoAshura asked
on
367 Views
Last Modified: 2012-05-11
Here is my problem, It only retrieves one entry insted of all of them from the database.

here is my code.

$sql = "SELECT cd.customer_name, c.first_name, c.last_name, cd.mobile_number, cd.model, cd.handset_supplied, cd.end_contract, cd.IMEI
FROM customer_details cd
LEFT JOIN customer c
  ON c.customer_name = cd.customer_name
WHERE c.account_number = '" . $val . "'";

It is only returning one results when i have two mobile numbers in the database with the same account number please help

Many thanks
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
move the WHERE coindition to the JOIN, otherwise it makes the LEFT JOIN a INNER JOIN implicitly
$sql = "SELECT cd.customer_name, c.first_name, c.last_name, cd.mobile_number, cd.model, cd.handset_supplied, cd.end_contract, cd.IMEI 
FROM customer_details cd
LEFT JOIN customer c 
  ON c.customer_name = cd.customer_name 
 AND c.account_number = '" . $val . "'";

Open in new window

theGhost_k8Database Consultant
CERTIFIED EXPERT

Commented:
and c.customer_name = cd.customer_name?
Do both customer_name for account number are same?

Author

Commented:
Hi thanks for reply,

I tried your query angellll and still only get one entry back from the database.

both c.customer_name and account_number are there same in both tables.

customer table has customer_name and account_number

customer_details has customer_name and account_number also but also has mobile_number

all mobiles for that person should be retrieved in the query where the account number is the same as  the one in the customer table.
CERTIFIED EXPERT

Commented:
what is result of this query

select * from customer_details
where account_number = '" . $val . "'";

is it results 2 mobile numbers ?

Author

Commented:
no it only returns one but there is deffinatly 2 in my database. with the same account_number

Author

Commented:
see image

of customer_details
Untitled.png
CERTIFIED EXPERT

Commented:
try this

select * from customer_details
where Trim(account_number) = '4413668';

if 2 then make the same in your query

Author

Commented:
when i did the query in my database it returned two entires when i did it php it only did one again? what the heck?

Author

Commented:
here is my full php code hopefully this might  help
<?php 
error_reporting(E_ALL);
 if(isset($_POST['submit'])){ 
	  if(isset($_GET['go'])){ 
	if(preg_match("/^[  a-zA-Z0-9]+/", $_POST['searchterm'])){ 
	  $searchterm=$_POST['searchterm']; 
	  //connect  to the database 
	include 'myphp.php';
	  //-query  the database table 
	  
	  $query = "SELECT c.customer_name, c.network, cd.mobile_number, cd.staff_member
FROM customer c
LEFT JOIN customer_details cd
  ON c.customer_name = cd.customer_name 
  WHERE
c.customer_name LIKE '%" . $searchterm .    "%' OR cd.mobile_number LIKE '%" . $searchterm .  "%' OR cd.staff_member LIKE '%" . $searchterm .  "%' OR c.network LIKE '%" . $searchterm .  "%'
GROUP BY c.customer_name"; 
$result = mysql_query($query) or die(mysql_error() . " IN $query");
	  
	 // $sql="SELECT mobile_number, customer_name, network, staff_member FROM customer_details, customer WHERE customer_name LIKE '%" . $searchterm .    "%' OR mobile_number LIKE '%" . $searchterm .  "%' OR staff_member LIKE '%" . $searchterm .  "%' OR network LIKE '%" . $searchterm .  "%'"; 
	  //-run  the query against the mysql query function 
	  $result=mysql_query($query) or die(mysql_error());
	   
	  //-create  while loop and loop through result set 
	  while($row=mysql_fetch_array($result)){ 
	          $customer_name  =$row['customer_name']; 
	          $mobile_number =$row['mobile_number']; 
	           	 
	  //-display the result of the array 
	  echo "<ul>\n"; 
	  echo "<li>" . "<a  href=\"mobilenum.php?val=$customer_name\">"   .$customer_name ."</a></li>\n"; 
	  echo "</ul>"; 
	  } 
	  } 
	  else{ 
	  echo  "<p>Please enter a search query</p>"; 
	  } 
	  } 
	  } 
	?>

Open in new window

CERTIFIED EXPERT

Commented:
try this

$sql = "SELECT cd.customer_name, c.first_name, c.last_name, cd.mobile_number, cd.model, cd.handset_supplied, cd.end_contract, cd.IMEI
FROM customer_details cd
Inner JOIN customer c
  ON Trim(c.account_number)= Trim(cd.account_number)
WHERE Trim(c.account_number) = '" . $val . "'";
CERTIFIED EXPERT

Commented:
I havn't seen the the query you asked in your code ??

Author

Commented:
apologies was the wrong page. here is the right one
<?php 
error_reporting(E_ALL);
include 'myphp.php';
$val = $_GET['val'];

$sql = "SELECT cd.customer_name, c.first_name, c.last_name, cd.mobile_number, cd.model, cd.handset_supplied, cd.end_contract, cd.IMEI 
FROM customer_details cd
Inner JOIN customer c 
  ON Trim(c.account_number)= Trim(cd.account_number)
WHERE Trim(c.account_number) = '" . $val . "'";
//$sql ="select * from customer_details
//where  Trim(account_number) = '4413668';
//$sql = "SELECT cd.customer_name, c.first_name, c.last_name, cd.mobile_number, cd.model, cd.handset_supplied, cd.end_contract, cd.IMEI 
//FROM customer_details cd
//LEFT JOIN customer c 
//ON c.customer_name = cd.customer_name 
//WHERE c.account_number = '" . $val . "'";
//$sql = "SELECT cd.customer_name, c.first_name, c.last_name, cd.mobile_number, cd.model, cd.handset_supplied, cd.end_contract, cd.IMEI 
//FROM customer_details cd
//LEFT JOIN customer c 
//  ON c.customer_name = cd.customer_name 
//WHERE c.account_number = '" . $val . "'";
$result = mysql_query($sql) or die(mysql_error() . " IN $sql");

 
$rs = mysql_query($sql);
$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>
//</table>";
while($row = mysql_fetch_array($rs))
{
   echo"<table>";
  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 "<tr><td>Date Hand Set Supplied:</td><td>".$row['handset_supplied']."</td></tr>";
   echo "<tr><td>Contract End Date:</td><td>".$row['end_contract']."</td></tr>";
   echo "<tr><td>IMEI Number:</td><td>".$row['IMEI']."</td></tr>";
   echo"</table>";
}
   ?>

Open in new window

Author

Commented:
also your query did not work

Author

Commented:
im at a loss with this i really dont know what im doing wrong
CERTIFIED EXPERT

Commented:
remove line before while

$row = mysql_fetch_array($rs);

try this
<?php 
error_reporting(E_ALL);
include 'myphp.php';
$val = $_GET['val'];

$sql = "SELECT cd.customer_name, c.first_name, c.last_name, cd.mobile_number, cd.model, cd.handset_supplied, cd.end_contract, cd.IMEI 
FROM customer_details cd
Inner JOIN customer c 
  ON Trim(c.account_number)= Trim(cd.account_number)
WHERE Trim(c.account_number) = '" . $val . "'";

$result = mysql_query($sql) or die(mysql_error() . " IN $sql");

 
$rs = mysql_query($sql);

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>
//</table>";
while($row = mysql_fetch_array($rs))
{
   echo"<table>";
  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 "<tr><td>Date Hand Set Supplied:</td><td>".$row['handset_supplied']."</td></tr>";
   echo "<tr><td>Contract End Date:</td><td>".$row['end_contract']."</td></tr>";
   echo "<tr><td>IMEI Number:</td><td>".$row['IMEI']."</td></tr>";
   echo"</table>";
}
   ?>

Open in new window

Author

Commented:
now i get Notice: Undefined variable: row in G:\EasyPHP-5.3.5.0\www\account.php on line 100

Author

Commented:
line 100 is echo "<tr><td>Customer:</td><td>".$row['customer_name']."</td></tr>";
CERTIFIED EXPERT

Commented:
try thuis
ON Trim(c.account_number)= Trim(cd.account_number)
WHERE Trim(c.account_number) = '" . $val . "'";

$result = mysql_query($sql) or die(mysql_error() . " IN $sql");
$i=0;
$row = mysql_fetch_array($rs)
$num=mysql_numrows($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>
//</table>";
while ($i < $num) {
{
   echo"<table>";
  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 "<tr><td>Date Hand Set Supplied:</td><td>".$row['handset_supplied']."</td></tr>";
   echo "<tr><td>Contract End Date:</td><td>".$row['end_contract']."</td></tr>";
   echo "<tr><td>IMEI Number:</td><td>".$row['IMEI']."</td></tr>";
   echo"</table>";
$row = mysql_fetch_array($rs)


$i++;
}
   ?>

Open in new window

Author

Commented:
Parse error: syntax error, unexpected T_VARIABLE in G:\EasyPHP-5.3.5.0\www\account.php on line 97

Author

Commented:
line 97 is $num = mysql_numrows($rs);

i changed it to $num = mysql_num_rows($rs); same error
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
same error as above unexpected Tvariable on line 99

$num=mysql_numrows($rs); is line 99

Author

Commented:
sorted it, I was missing out the ; in your code.

Thanks again you have just saved me..

Major presentation and that bug had nearly foiled me.

thanks again.

Author

Commented:
absolute life saver
CERTIFIED EXPERT

Commented:
Glad to help you
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.