PHP Dropdown Menu , how to pass a Customer ID to a new PHP based query

jamesflowers · July 26, 2014, 5:53am

hi

I have a dropdown menu that reads from a MYSQL databse and displays customer names (included below).

How would I use this dropdown to pass a CustomerID to a second PHP page that contains a query that would select further information about chosen customer.

Many Thanks James

[php]

<?php //------------------------------------------------------------------------------------------------------------------------------------------------------ //James Flowers 2014 //------------------------------------------------------------------------------------------------------------------------------------------------------ //database configuration $config['mysql_host'] = "xxx"; $config['mysql_user'] = "xxx"; $config['mysql_pass'] = "xxx"; $config['db_name'] = "xxx"; //------------------------------------------------------------------------------------------------------------------------------------------------------ mysql_connect($config['mysql_host'],$config['mysql_user'],$config['mysql_pass']);//connect to host //------------------------------------------------------------------------------------------------------------------------------------------------------ @mysql_select_db($config['db_name']) or die( "Unable to select database"); //select database //------------------------------------------------------------------------------------------------------------------------------------------------------ $sql ="select customerName , customerNumber from customers"; //SQL goes here $result = mysql_query($sql); if (!$result) {die('Invalid query: ' . mysql_error()); } $result = mysql_query($sql) or die(mysql_error()); $dropdown = ""; while($row = mysql_fetch_assoc($result)) {$dropdown .= "\r\n{$row['customerName']}";} $dropdown .= "\r\n"; ?> <?php echo $dropdown; ?>
[/php]

Strider64 · July 26, 2014, 10:36am

First thing don’t suppress errors with @ (I don’t know why anyone who teaches PHP doe this >:()

[php]@mysql_select_db($config[‘db_name’]) or die( “Unable to select database”); //select database[/php]

Second, I personally would get rid of this

[php] //database configuration
$config[‘mysql_host’] = “xxx”;
$config[‘mysql_user’] = “xxx”;
$config[‘mysql_pass’] = “xxx”;
$config[‘db_name’] = “xxx”;[/php]

and just have all that in your connection string.

Third, don’t use mysql, use either mysqli or PDO (My Recommendation) for mysql is depreciated.

I might get back and help you with the tag, but someone else might help you in the meantime.

Strider64 · July 26, 2014, 11:29am

It’s not perfect, but it works. The code can be tighten up some more.

[php] /* Create the database connection as a PDO Object */
try
{
$db_options = array(
PDO::ATTR_EMULATE_PREPARES => false // important! use actual prepared statements (default: emulate prepared statements)
, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION // throw exceptions on errors (default: stay silent)
, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC // fetch associative arrays (default: mixed arrays)
);
$pdo = new PDO(‘mysql:host=localhost;dbname=customers;charset=utf8’, ‘root’, ‘your_password’, $db_options);
}
catch(PDOException $e)
{ // Report the Error:
// Never a good idea to display error messages to the user, this is just for the example:
echo $errMsg = “Something is not right, check your php.ini settings or code.”;
}

try {	
	$query = "SELECT customerNumber, customerName FROM customers ORDER BY customerName ASC";
	$stmt = $pdo->prepare($query);
	$stmt->execute();
	
} catch (Exception $e) { // Catch generic exceptions
}

?>

Dropdown <?php /* Fetch the Usernames */ while ( $row = $stmt->fetch(PDO::FETCH_ASSOC) ) { echo '' . $row['customerName'] . ''; } ?> [/php]

jamesflowers · July 26, 2014, 2:32pm

ok , so I used MYSQLi (as I understand that easier than the PDO)

and created this

[php]

<?php include ("dbconn.php"); // CONNECT TO THE DATABASE if (mysqli_connect_errno()) { printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } $query = "SELECT * FROM customers "; $result = $mysqli->query($query) or die($mysqli->error.__LINE__); // GOING THROUGH THE DATA if($result->num_rows > 0) { while($row = $result->fetch_assoc()) { echo stripslashes($row['customerName']); echo stripslashes($row['customerNumber']); } } else { echo 'NO RESULTS'; } // CLOSE CONNECTION mysqli_close($mysqli); ?>

[/php]

I know I have to pass the results to

Dropdown <?php

what goes in here? Im not sure of this bit and how do you pass customerNumber by choosing a customerName?

?>

benanamen · July 26, 2014, 3:09pm

Your problem is simple. The value of the selection should actually be the customer ID, not the customer name. PDO is not hard by the way.

Strider64 · July 26, 2014, 3:18pm

OK, I am a little unsure what you are asking, so I am taking a guess. This is what I would do:

The filename is dropdown2.php:
[php]<?php
if ( isset($_POST[‘submit’]) && $_POST[‘submit’] == ‘submit’) {
/* This is executed when the user clicks on submit */
$customerNo = $_POST[‘users’];

}

$mysqli = new mysqli(“localhost”, “your_username”, “your_password”, “database_name”);

/* check connection */
if ($mysqli->connect_errno) {
printf(“Connect failed: %s\n”, $mysqli->connect_error);
exit();
}

$query = “SELECT customerNumber, customerName FROM customers ORDER by customerName ASC”;

?>

Dropdown

<?= (isset($customerNo)) ? $customerNo : "Grab Customer No"; ?>

<?php if ($result = $mysqli->query($query)) {

/* fetch associative array */
while ($row = $result->fetch_assoc()) {
		  /* Put Customer Number in value in the option attribute */
    echo '<option value="' . $row['customerNumber'] . '">'. $row['customerNumber'] . ' ' . $row['customerName'] . '</option>'. PHP_EOL;
}

/* free result set */
$result->free();

}

/* close connection */
$mysqli->close();

?>

[/php]

If I guessed wrong then just try to clarify it a little bit better and btw you don’t have to worry about stripslashes for the database table isn’t being written to and besides that is one of the main reasons in using mysqli or PDO for you can use prepared statements when you do write to the database table.

Also the customer id should be auto incremented and the primary key (it usually is most of the time) in the database table setup.

jamesflowers · July 26, 2014, 3:20pm

well lets go with Mysqli for now

what goes in the dropdown section though, I cant find any examples.

Thanks

jamesflowers · July 26, 2014, 3:42pm

cool

I want a dropdown box to select from the database a customer name , and then pass the associated ID of that customer to a new page that selects more data about that custome (address etc).

Strider64 · July 26, 2014, 5:13pm

Well, you’re almost there.

All you would have to do in the second script I gave you is cut this out from the script and put it in a new php file:

[php]
if ( isset($_POST[‘submit’]) && $_POST[‘submit’] == ‘submit’) {
/* This is executed when the user clicks on submit */
$customerNo = $_POST[‘users’];
}
[/php]

and change this

[php][/php]

which was referring to the sampe php page to

[php][/php]

Then you can either access the same database table or a corresponding table with the extended information via the $customerNo (or whatever you called the customer id). Though I’m not MySQL expert and there are plenty of people out there that can give you really in-depth help on database optimization if you so desired. 8)

jamesflowers · July 27, 2014, 4:31am

Many thanks , solved a great deal.

Thanks