What is wrong with my query?

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
LVL 6
NeoAshuraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
theGhost_k8Database ConsultantCommented:
and c.customer_name = cd.customer_name?
Do both customer_name for account number are same?
0
NeoAshuraAuthor 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.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Pratima PharandeCommented:
what is result of this query

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

is it results 2 mobile numbers ?
0
NeoAshuraAuthor Commented:
no it only returns one but there is deffinatly 2 in my database. with the same account_number
0
NeoAshuraAuthor Commented:
see image

of customer_details
Untitled.png
0
Pratima PharandeCommented:
try this

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

if 2 then make the same in your query
0
NeoAshuraAuthor 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?
0
NeoAshuraAuthor 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

0
Pratima PharandeCommented:
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 . "'";
0
Pratima PharandeCommented:
I havn't seen the the query you asked in your code ??
0
NeoAshuraAuthor 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

0
NeoAshuraAuthor Commented:
also your query did not work
0
NeoAshuraAuthor Commented:
im at a loss with this i really dont know what im doing wrong
0
Pratima PharandeCommented:
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

0
NeoAshuraAuthor Commented:
now i get Notice: Undefined variable: row in G:\EasyPHP-5.3.5.0\www\account.php on line 100
0
NeoAshuraAuthor Commented:
line 100 is echo "<tr><td>Customer:</td><td>".$row['customer_name']."</td></tr>";
0
Pratima PharandeCommented:
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

0
NeoAshuraAuthor Commented:
Parse error: syntax error, unexpected T_VARIABLE in G:\EasyPHP-5.3.5.0\www\account.php on line 97
0
NeoAshuraAuthor Commented:
line 97 is $num = mysql_numrows($rs);

i changed it to $num = mysql_num_rows($rs); same error
0
Pratima PharandeCommented:
<?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");
$i=0;
$rs = mysql_query($sql);

$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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NeoAshuraAuthor Commented:
same error as above unexpected Tvariable on line 99

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

0
NeoAshuraAuthor 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.
0
NeoAshuraAuthor Commented:
absolute life saver
0
Pratima PharandeCommented:
Glad to help you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.