• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 325
  • Last Modified:

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
0
NeoAshura
Asked:
NeoAshura
1 Solution
 
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
 
K VDatabase 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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
 
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

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now