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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Moonspell
Forum Newbie
Posts: 21
Joined: Tue May 13, 2003 4:54 pm

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

Post 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.)
User avatar
discobean
Forum Commoner
Posts: 49
Joined: Sun May 18, 2003 9:06 pm
Location: Sydney, Australia
Contact:

Post 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:
User avatar
patrikG
DevNet Master
Posts: 4235
Joined: Thu Aug 15, 2002 5:53 am
Location: Sussex, UK

Post by patrikG »

<-- as well
Black Unicorn
Forum Commoner
Posts: 48
Joined: Mon Jun 16, 2003 9:19 am
Location: United Kingdom

SQL problem?

Post 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
Moonspell
Forum Newbie
Posts: 21
Joined: Tue May 13, 2003 4:54 pm

Post 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>
?>
User avatar
redcircle
Forum Commoner
Posts: 43
Joined: Fri Jan 31, 2003 8:47 pm
Location: michigan, usa

Post 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
Moonspell
Forum Newbie
Posts: 21
Joined: Tue May 13, 2003 4:54 pm

Post 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.
User avatar
redcircle
Forum Commoner
Posts: 43
Joined: Fri Jan 31, 2003 8:47 pm
Location: michigan, usa

Post 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.
Moonspell
Forum Newbie
Posts: 21
Joined: Tue May 13, 2003 4:54 pm

Post by Moonspell »

Success. Thank you very much.
Moonspell
Forum Newbie
Posts: 21
Joined: Tue May 13, 2003 4:54 pm

Post 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)
SBukoski
Forum Contributor
Posts: 128
Joined: Wed May 21, 2003 10:39 pm
Location: Worcester, MA

Post by SBukoski »

Use this SQL syntax:

Code: Select all

$query = 'select distinct(salesmen_name) from salesmen where salesmen_id = "'.$salesmen_id.'"';
User avatar
redcircle
Forum Commoner
Posts: 43
Joined: Fri Jan 31, 2003 8:47 pm
Location: michigan, usa

Post 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>
Moonspell
Forum Newbie
Posts: 21
Joined: Tue May 13, 2003 4:54 pm

Post 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>';
} 
} 

?>
Moonspell
Forum Newbie
Posts: 21
Joined: Tue May 13, 2003 4:54 pm

Post 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>';
} 



?>
?>
Post Reply