SQL and PHP Problem Please Help

pctechtime

Below is what I have so far. My problem is I want select the clients name and number and view all of the infomation stored for that client in all the rows in the database. This must be in a table format with headers that I can name. Example clients_name would be Customers Name. Thanks for your help. PS the print statements on the bottom are there only to see if the data has been passed.

Mike

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
<style type="text/css">

</style>
</head>

<body>
<div align="center">
<h1 class="style1">Coin Database </h1>
<h2 class="style1">Data Entry </h2>
</div>
<p align="center" class="style2">Result List </p>

<?php
$usr = "888";
$pwd = "888";
$db = "888";
$host = "localhost";

// # connect to database
$cid = mysql_connect($host,$usr,$pwd);
mysql_select_db($db);
if (mysql_error()) { print "Database ERROR: " . mysql_error(); }

// Create the pull-down menu information.
$query1 = "SELECT order_number,client_name,test_ordered,salesperson,order_date FROM orders ORDER BY order_number ASC";

$result1 = @ ($query1);

$pulldown1 = '<option>Select One</option>';

while ($row = mysql_fetch_array ($result1, MYSQL_ASSOC)) {

$pulldown1 .= "<option value=\"{$row['order_number']}\">{$row['order_number']}) {$row['test_ordered']}</option>\n";

}
?>

<form name="orders" action="orderlookup.php" method="POST">

<table> <tr><td>Order Name: </td><td>
<select name="order_number">
<?php echo $pulldown1; ?> </select>
</td></tr></table>
<p><input type="submit" value="View Results"></p>
</th></tr></table>
</form>

<?php

// this is processed when the form is submitted
// back on to this page (POST METHOD)
if ($_SERVER['REQUEST_METHOD'] == "POST")
{

    # escape data and set variables
    $order_number = addslashes($_POST["order_number"]);
    $client_name = addslashes($_POST["client_name"]);
    $test_ordered = addslashes($_POST["test_ordered"]);
    $salesperson = addslashes($_POST["salesperson"]);
    $order_date = addslashes($_POST["order_date"]);

print "<p><font color=red>$order_number[/url]</font></p>";
print "<p><font color=red>$client_name[/url]</font></p>";
print "<p><font color=red>$test_ordered[/url]</font></p>";
print "<p><font color=red>$salesperson[/url]</font></p>";
print "<p><font color=red>$order_date[/url]</font></p>";
print "<p><font color=red>$pulldown2[/url]</font></p>";

}
?>

</body>
</html>

leatherback

Hi,

Use this query:
$query1 = "SELECT order_number,client_name,test_ordered,salesperson,order_date FROM orders ORDER BY order_number ASC";

and adjust it to:

$query1 = "SELECT order_number,client_name,test_ordered,salesperson,order_date FROM orders where client_name='client_name' ORDER BY order_number ASC";

pctechtime

This doesn't work. When I use it I lose my drop down box data.

leatherback

Hm.. I do not hope you modified the actual section which you have working, did you?

What I meant is, use the query you have as an example, and work from there as a base for your page output..

So you have this

// Create the pull-down menu information.
$query1 = "SELECT order_number,client_name,test_ordered,salesperson,order_date FROM orders ORDER BY order_number ASC";

$result1 = @mysql_query ($query1);

$pulldown1 = '<option>Select One</option>';

while ($row = mysql_fetch_array ($result1, MYSQL_ASSOC)) {

$pulldown1 .= "<option value=\"{$row['order_number']}\">{$row['order_number']}) {$row['test_ordered']}</option>\n";

}

I assumed you wrote that yourself, and therefore have some idea on how to get data into your page.

Perhaps I should ask you: Which part is giving you a headache? The query, or creating the output?

pctechtime

Here is some new code. What I can't do is the following along with what I want to do.

Pull a list of all order_number in a drop down box. This part works.

Then whatever selection/order_number I select I want to pull all the all the colums for that row that I selected.

Here is the current code.

<?php
$usr = "";
$pwd = "";
$db = "***";
$host = "localhost";

// # connect to database
$cid = mysql_connect($host,$usr,$pwd);
mysql_select_db($db);
if (mysql_error()) { print "Database ERROR: " . mysql_error(); }

// Create the pull-down menu information.
$query1 = "SELECT * FROM orders ORDER BY order_number ASC";

$result1 = @ ($query1);

$pulldown1 = '<option>Select One</option>';

while ($row = mysql_fetch_array ($result1, MYSQL_ASSOC)) {
$pulldown1 .= "<option value=\"{$row['order_number']}\">{$row['order_number']}</option>\n";

}
print "<p><font color=red>$order_number</a></font></p>";

<form name="orders" action="orderlookup.php" method="POST">

<table> <tr><td><b>Order Number: </b></td><td>
<select name="order_number">
<?php echo $pulldown1; ?> </select>
</td></tr></table>
<p><input type="submit" value="View Results"></p>
</th></tr></table>
</form>

<?php
$query2 = "SELECT * FROM orders WHERE order_number='$order_number' ";
$results = mysql_query($query2);
echo "<table><tr>
<th>Order Number</th>
<th>Client Name</th>
<th>Test Ordered</th>
<th>Date Ordered</th>
<th>Sales Person</th>
</tr>";
while ($row = mysql_fetch_array($results))
{
echo "<tr><td>" . $row['order_number'] . "</td><br><td>" . $row['client_name'] . "</td><td>" . $row['test_ordered'] . "</td><td>" . $row['order_date'] . "</td><td>" . $row['salesperson'] . "</td>
</tr>";
}
echo "</table>\n";
print "<p><font color=red>$order_number</a></font></p>";

</body>
</html>

leatherback

On how many pages does this code reside? I am assuming you -at the moment- are using two separate pages, one with the dropdown, and one with the results display?
If you are not, then you need to test whether a variable has actually been posted to the script, because PhP doesn't 'know' this.
Second: You need to retrieve the variables from the posted data array.

In very short:


<?php
$usr = "***";
$pwd = "***";
$db = "***";
$host = "localhost";

// # connect to database
$cid = mysql_connect($host,$usr,$pwd);
mysql_select_db($db);
if (mysql_error()) { print "Database ERROR: " . mysql_error(); }

// Create the pull-down menu information.
$query1 = "SELECT * FROM orders ORDER BY order_number ASC";

$result1 = mysql_query ($query1) or die('Hm.. something wrong here');

$pulldown1 ="";

while ($row = mysql_fetch_array ($result1)) {
$pulldown1 .= "<option value=\"{$row['order_number']}\">{$row['order_number']}</option>\n";

}

// Doesn't the following line give you an error?
print "<p><font color=red>$order_number</a></font></p>";

?>

<form name="orders" action="orderlookup.php" method="POST">
<table> <tr><td><b>Order Number: </b></td><td>
<select name="order_number">
<?php echo $pulldown1; ?> </select>
</td></tr></table>
<p><input type="submit" value="View Results"></p>
</th></tr></table>
</form>


<?php

if(array_key_exists('order_number', $_POST)) // check whether submitted
  {
  if(is_numeric($_POST['order_number'])
    {
    $order_number = $_POST['order_number'];
    }
  else  // Give an alternative. If they are messing with your script, you'll be laughing
    {
    $order_number = 1;
    }

$query2 = "SELECT * FROM orders WHERE order_number='$order_number' ";
$results = mysql_query($query2);
echo "<table><tr>
<th>Order Number</th>
<th>Client Name</th>
<th>Test Ordered</th>
<th>Date Ordered</th>
<th>Sales Person</th>
</tr>";
while ($row = mysql_fetch_array($results))
{
echo "<tr><td>" . $row['order_number'] . "</td><br><td>" . $row['client_name'] . "</td><td>" . $row['test_ordered'] . "</td><td>" . $row['order_date'] . "</td><td>" . $row['salesperson'] . "</td>
</tr>";
}
echo "</table>\n";
print "<p><font color=red>$order_number</a></font></p>";
}  // End of posted section
?>

pctechtime

Look below I get an error. I have commented the error. Also I noticed that the Select was removed from my drop down box WHY? Also should the produce what I am looking for?

Thanks for everyones help.

<?php
$usr = "";
$pwd = "";
$db = "***";
$host = "localhost";

// # connect to database
$cid = mysql_connect($host,$usr,$pwd);
mysql_select_db($db);
if (mysql_error()) { print "Database ERROR: " . mysql_error(); }

// Create the pull-down menu information.
$query1 = "SELECT * FROM orders ORDER BY order_number ASC";

$result1 = mysql_query ($query1) or die('Hm.. something wrong here');

$pulldown1 ="";

while ($row = mysql_fetch_array ($result1)) {
$pulldown1 .= "<option value=\"{$row['order_number']}\">{$row['order_number']}</option>\n";

}

<form name="orders" action="orderlookup.php" method="POST">
<table> <tr><td><b>Order Number: </b></td><td>
<select name="order_number">
<?php echo $pulldown1; ?> </select>
</td></tr></table>
<p><input type="submit" value="View Results"></p>
</th></tr></table>
</form>

<?php

if(array_key_exists('order_number', $POST)) // check whether submitted
{
if(is_numeric($POST['order_number'])
{ // I get an error here "Parse error: syntax error, unexpected '{' in /home/michael4/public_html/pages/lookup/orderlookup.php on line 67"
$order_number = $_POST['order_number'];
}
else // Give an alternative. If they are messing with your script, you'll be laughing
{
$order_number = 1;
}

$query2 = "SELECT * FROM orders WHERE order_number='$order_number' ";
$results = mysql_query($query2);
echo "<table><tr>
<th>Order Number</th>
<th>Client Name</th>
<th>Test Ordered</th>
<th>Date Ordered</th>
<th>Sales Person</th>
</tr>";
while ($row = mysql_fetch_array($results))
{
echo "<tr><td>" . $row['order_number'] . "</td><br><td>" . $row['client_name'] . "</td><td>" . $row['test_ordered'] . "</td><td>" . $row['order_date'] . "</td><td>" . $row['salesperson'] . "</td>
</tr>";
}
echo "</table>\n";

}

</body>
</html>

leatherback

This line:
if(is_numeric($_POST['order_number'])
is missing a ) bracket:

if(is_numeric($_POST['order_number']))