Page 1 of 1

I'm getting paid to do this, and I'm in TROUBLE PLEASE HELP!

Posted: Tue Jun 17, 2003 3:34 am
by Moonspell
I need to take data from SQL tables and organize them. Basically, I search for a match between to tables, and then I put one under the table of another. It is putting customers in the array of the person who served the customer. Array jack should be like:

Jack Smith
- Jenny
- Bob
- etc.

Assume my SQL queries are correct. What is wrong with this? It displays nothing.

Code: Select all

<?php
session_start();

require("checksession.inc");
require("classoperations.inc");

###########
// Make sure session is valid
$seschecker = new SessionClass();
$seschecker->CheckStatus();
// Finished
###########

$loginobject = new sqlconnect();
?>

<html>
<head>
<title>Results</title>
</head>

<body>
<img src="nav_db_r1_c1.gif"><br><br>

<a href="login.php">Click to return</a>
<br><br>
<table>
<?php
	 
	 // Find the current user's ClientNameID by comparing
	 // Their session value with the database
	 // Thx to DevNet for this!
	 
	 $result = mysql_query("SELECT * FROM ClientLogin WHERE Username = '$valid_user'");
	 				 while ($info = mysql_fetch_array($result)) {

	 				 $id = $info['ClientNameID'];

	 				 } 
					 
	 // Now $id should hold their ClientNameID record

// Find number of rows
$result = mysql_query("SELECT * FROM CustomerEntry
WHERE ClientNameID =  $id");
$num_rows = mysql_num_rows($result);

######################################################
##            FORMAT FOR WHILE LOOP                 ##
######################################################
##  $x = 1;                                         ##
##  $num_rows++;                                    ##
##                                                  ##
##  while ( $x < $num_rows ) {                      ##
##	  		... do stuff                              ##
##		  	                                          ##
##		  	$x++;                                     ##
##  }                                               ##
######################################################


$x = 34;
$num_rows++;

while ( $x < $num_rows ) {
			
			// $x starts at 34 (the first row) and ends at lets say 37
			// which will be the last row
			
			$result = mysql_query("SELECT * FROM CustomerEntry
														 WHERE CustomerEntryID = $x");
														 
			while ($info = mysql_fetch_array($result)) {

	 				 $ClientSalesID = $info['ClientSalesID'];
					 $CFName = $info['FName'];
					 $CLName = $info['LName'];

	 	  } 				
						 
			// Has data, now compare to other table
			
			$result = mysql_query("SELECT * FROM ClientSales
														 WHERE ClientSalesID = $ClientSalesID");
			
			while ($info = mysql_fetch_array($result)) {
			
						// We have a match.  We must add it to a multi-dimensional
						// array with the opening as the salespersons name
						
						
						// Now we grab the selected FName and LName for the array
						
						$queryname = mysql_query("SELECT SalesFName, SalesLName
											 	 							FROM ClientSales
																			WHERE ClientSalesID = $ClientSalesID");
						
						while ($info = mysql_fetch_array($queryname)) {
						
									$FName = $info['SalesFName'];
									$LName = $info['SalesLName'];
						}
						
						// Format data
						$arrayname = $FName." ".$LName;
						$cname = $CFName." ".$CLName;
						
						// Insert		
						$arrayname[] = $cname;
					  }
						
						// Create an array to hold all the arrays [OMG!!!!!]
						
						$totalusers[] = $arrayname;
						
			$x++;
}

// Now this script has been ran.  All the matches have been paired up.  They
// have been put in their own little array which is owned by the salesperson.
// Then, that array name is added to the ultimate array list.  Now it is time
// to take the ultimate array list, loop through it to get each array in the
// array list, and then loops through THAT array to get to our data.  Basically
// it is arrays without them being inside themselves

// Count the size of the array
// Which should be the number of rows
$countofarray = count($totalusers);

foreach ( $totalusers as $val ) {
				
				// $val contains the array name
				// Count size of $val
				
				$countofval = count($val);
				print "<tr rowspan=$countofval>";
				print "<td>$val</td>";
				foreach ( $val as $val2 ) {
								print "<td>$val2</td>";
								print "</tr><tr>";
				}
				print "</tr>";
}

?>
</table>
(Admin Edit Note: We put the PHP BBCode tag in there for a reason. Please be kind and use it.)

Posted: Tue Jun 17, 2003 6:50 am
by discobean
Sorry, but maybe I would be more inclined to help if you had taken the effort to put your code in some [syntax=php]/*code goes here*/[/syntax] BB Tags :roll:

Posted: Tue Jun 17, 2003 8:23 am
by patrikG
<-- as well

SQL problem?

Posted: Tue Jun 17, 2003 8:38 am
by Black Unicorn
I am very beginner at PhP, so I might not have spotted any defects with proposed script. However,

Code: Select all

<?php 
$result = mysql_query("SELECT * FROM ClientLogin WHERE Username = '$valid_user'"); 
while ($info = mysql_fetch_array($result)){ 
    $id = $info['ClientNameID']; 
    } 
?>
Using while (blah){ ... } seems like a bad idea if you're only after one username. Just do a
$id = @mysql_fetch_array($result);
$id = $id["ClientNameID"];
and it won't be blank after the thing recycles only to put an empty result into the $id variable.

I am not certain if this is where the problem is with your code, but you never know.
Sincerely,
H

Posted: Tue Jun 17, 2003 3:16 pm
by Moonspell
Here's it in code:

Code: Select all

<?php
session_start();

require("checksession.inc");
require("classoperations.inc");

###########
// Make sure session is valid
$seschecker = new SessionClass();
$seschecker->CheckStatus();
// Finished
###########

$loginobject = new sqlconnect();
?>

<html>
<head>
<title>Results</title>
</head>

<body>
<img src="nav_db_r1_c1.gif"><br><br>

<a href="login.php">Click to return</a>
<br><br>
<table>
<?php

// Find the current user's ClientNameID by comparing
// Their session value with the database
// Thx to DevNet for this!

$result = mysql_query("SELECT * FROM ClientLogin WHERE Username = '$valid_user'");
while ($info = mysql_fetch_array($result)) {

$id = $info['ClientNameID'];

}

// Now $id should hold their ClientNameID record

// Find number of rows
$result = mysql_query("SELECT * FROM CustomerEntry
WHERE ClientNameID = $id");
$num_rows = mysql_num_rows($result);

######################################################
## FORMAT FOR WHILE LOOP ##
######################################################
## $x = 1; ##
## $num_rows++; ##
## ##
## while ( $x < $num_rows ) { ##
## ... do stuff ##
## ##
## $x++; ##
## } ##
######################################################


$x = 34;
$num_rows++;

while ( $x < $num_rows ) {

// $x starts at 34 (the first row) and ends at lets say 37
// which will be the last row

$result = mysql_query("SELECT * FROM CustomerEntry
WHERE CustomerEntryID = $x");

while ($info = mysql_fetch_array($result)) {

$ClientSalesID = $info['ClientSalesID'];
$CFName = $info['FName'];
$CLName = $info['LName'];

}

// Has data, now compare to other table

$result = mysql_query("SELECT * FROM ClientSales
WHERE ClientSalesID = $ClientSalesID");

while ($info = mysql_fetch_array($result)) {

// We have a match. We must add it to a multi-dimensional
// array with the opening as the salespersons name


// Now we grab the selected FName and LName for the array

$queryname = mysql_query("SELECT SalesFName, SalesLName
FROM ClientSales
WHERE ClientSalesID = $ClientSalesID");

while ($info = mysql_fetch_array($queryname)) {

$FName = $info['SalesFName'];
$LName = $info['SalesLName'];
}

// Format data
$arrayname = $FName." ".$LName;
$cname = $CFName." ".$CLName;

// Insert
$arrayname[] = $cname;
}

// Create an array to hold all the arrays [OMG!!!!!]

$totalusers[] = $arrayname;

$x++;
}

// Now this script has been ran. All the matches have been paired up. They
// have been put in their own little array which is owned by the salesperson.
// Then, that array name is added to the ultimate array list. Now it is time
// to take the ultimate array list, loop through it to get each array in the
// array list, and then loops through THAT array to get to our data. Basically
// it is arrays without them being inside themselves

// Count the size of the array
// Which should be the number of rows
$countofarray = count($totalusers);

foreach ( $totalusers as $val ) {

// $val contains the array name
// Count size of $val

$countofval = count($val);
print "<tr rowspan=$countofval>";
print "<td>$val</td>";
foreach ( $val as $val2 ) {
print "<td>$val2</td>";
print "</tr><tr>";
}
print "</tr>";
}

?>
</table>
?>

Posted: Tue Jun 17, 2003 5:14 pm
by redcircle
I'm highly confused of your code. You want to get all the customers of a specific salesmen and display that information correct?


two tables.

Customer
-customer_id
-salesmen_id
-customer_name
-customer_addr
-customer_etc


Salesmen
-salesmen_id
-salesmen_name

Code: Select all

<table>
<?php
$query = 'select salesmen_name from salesmen where salesmen_id = "'.$salesmen_id.'" limit 1';
$result = mysql_query($query);
$row = mysql_fetch_array($result);

echo '<tr><td>'.$row['salesmen_name'].'</td></tr>';
//gets the salesmen name and echo's it out.

$query = 'select customer_name from customer where salesmen_id = "'.$salesmen_id.'";
$result = mysql_query($query);
while($row=mysql_fetch_array($result))
{
echo '<tr><td>- '.$row['customer_name'].'</td></tr>';
}
?>
</table>
You are wasting your time putting them in an array unless you are going to do something else with them.

let me know if that helped at all

Posted: Tue Jun 17, 2003 5:39 pm
by Moonspell
That looks like it will work. What does the limit = 1 mean? Does that mean it won't display Brad Pitt twice? In the database, it has the salesperon name listed EACH TIME it has a new customer, so Brad Pitt might be listed 25 times. It should only display Brad Pitt once with every customer under his name.

Posted: Tue Jun 17, 2003 5:45 pm
by redcircle
limit 1 means it will only return 1 record. which is all that is needed. It will display the salesmen once. and then list through all the customers.

Posted: Tue Jun 17, 2003 6:53 pm
by Moonspell
Success. Thank you very much.

Posted: Wed Jun 18, 2003 2:21 am
by Moonspell
redcircle wrote:limit 1 means it will only return 1 record. which is all that is needed. It will display the salesmen once. and then list through all the customers.
How do I manipulate this code so that it displays information from each different salesman? Right now it's only displaying for the first salesman it finds, then stops. However, since there are many salesman in the table, I need to display each one. (and Brad Pitt may be listed 24 times)

Posted: Wed Jun 18, 2003 10:38 am
by SBukoski
Use this SQL syntax:

Code: Select all

$query = 'select distinct(salesmen_name) from salesmen where salesmen_id = "'.$salesmen_id.'"';

Posted: Wed Jun 18, 2003 1:50 pm
by redcircle
you will then need to use a while statement that will loop through all the salesmen.

Code: Select all

<table> 
<?php 
$query = 'select salesmen_name, salesmen_id from salesmen; 
$result = mysql_query($query); 
while($salesmen = mysql_fetch_array($result))
{ 

echo '<tr><td>'.$row['salesmen_name'].'</td></tr>'; 
//gets the salesmen name and echo's it out. 

$query = 'select customer_name from customer where salesmen_id = "'.$salesmen['salesmen_id'].'"; 
$customer_result = mysql_query($query); 
while($customer=mysql_fetch_array($customer_result)) 
{ 
echo '<tr><td>- '.$customer['customer_name'].'</td></tr>'; 
}
} 
?> 
</table>

Posted: Wed Jun 18, 2003 10:19 pm
by Moonspell
I'm getting an error at line 56:

Parse error: parse error in /www/a/asklucky/htdocs/grsalesperson.php on line 56

Code: Select all

<?php
<?php
	 
	 // Find the current user's ClientNameID by comparing
	 // Their session value with the database
	 // Thx to DevNet for this!
	 
	 $result = mysql_query("SELECT * FROM ClientLogin WHERE Username = '$valid_user'");
	 				 while ($info = mysql_fetch_array($result)) {

	 				 $id = $info['ClientNameID'];

	 				 } 
					 
	 // Now $id should hold their ClientNameID record

$query = 'select distinct(SalesLName) from ClientSales where ClientNameID = "'.$id.'"'; 
$result = mysql_query($query);

while($salesmen = mysql_fetch_array($result))
{

echo '<tr><td>'.$salesmen['SalesFName'].'</td></tr>';
//gets the salesmen name and echo's it out.

$query = 'select FName from CustomerEntry where ClientSalesID = "'.$salesmen['ClientSalesID'].'";
$customer_result = mysql_query($query);
while($customer=mysql_fetch_array($customer_result))
{
echo '<tr><td>- '.$customer['FName'].'</td></tr>';
} 
} 

?>

Posted: Wed Jun 18, 2003 10:59 pm
by Moonspell
I figured it out on my own. Actually, I just sloppied my code to make it work. I'm just posting this is case somebody searches, so they have a solution. (BTW, the codes were a little buggy and weren't consistent if somebody is reading this. However, they were enough to help me. Thank you very much)

Code: Select all

<?php
<?php
	 
	 // Find the current user's ClientNameID by comparing
	 // Their session value with the database
	 // Thx to DevNet for this!
	 
	 $result = mysql_query("SELECT * FROM ClientLogin WHERE Username = '$valid_user'");
	 				 while ($info = mysql_fetch_array($result)) {

	 				 $id = $info['ClientNameID'];

	 				 } 
					 
	 // Now $id should hold their ClientNameID record

$query = "select distinct(SalesLName) from ClientSales where ClientNameID = $id"; 
$result = mysql_query($query);

while($salesmen = mysql_fetch_array($result))
{
print '<table border=1 width=600 bordercolor=#000000>';
$s1 = $salesmen['SalesLName'];

$quer = mysql_query("SELECT * FROM ClientSales WHERE SalesLName = '$s1'");
$resul = mysql_fetch_array($quer);

$s2 = $resul['SalesFName'];

echo "<tr bgcolor=#990000><td><font color=#FF9933><b>Salesman:</b></font> <b><font color='WHITE'>$s2 $s1</font></td></tr>";
//gets the salesmen name and echo's it out.

$ssss = $resul['ClientSalesID'];

$q2uery = "select FName, LName from CustomerEntry where ClientSalesID = '$ssss'"
;
$customer_result = mysql_query($q2uery);

while($customer = mysql_fetch_array($customer_result))
{
$s4 = $customer['FName'];
$s5 = $customer['LName'];

echo "<tr><td>$s4 $s5</td></tr>";
} 
print '</table><br>';
} 



?>
?>