PHP SQL drop-down question

[Steven]

Hi there. Used to lurk around here a few years back, helping where I could in the HTML/CSS forum. Well, I'm back.

 

I'm working on a basic, in-house job log form. My knowledge level in PHP and SQL is very low, so I'm kind of stumbling blind. I've managed to make a form, get it to send new information to my DB, and made a results page so you can see a table with all the submitted information. There are a number of features I want to add, but I'll save those for a later post.

 

I made a drop-down list on the results page, here's the code:

$result = mysqli_query($con,"SELECT client FROM jobs");echo "<select name='client'>";while ($row = mysqli_fetch_array($result)) {	echo "<option value='" . $row['client'] . "'>" . $row['client'] . "</option>";}echo "</select>";

It's working fine. It makes a drop-down lists and populates it with all the clients from my DB.

 

However, here's the problem:

 

And I'm sure it has something to do with my DB structure. As of now, there are six or more "jobs" added to this joblog database. 3 jobs for "Client A", 1 job for "Client B" and 3 jobs for "Client C". The problem is the drop-down gets populated like this:

 

Client A

Client A

Client A

Client B

Client C

Client C

Client C

 

Not good. I want it to be populated like this:

 

Client A

Client B

Client C

 

The purpose of this drop-down list is so you can view all the clients in the database, hit one, and have all the jobs for that client returned to you. Somehow when the user submits a new job to the joblog, that client needs to be identified with a unique client?

 

I have no idea how to continue!

 

Thanks in advance,

Steve

[thescientist]

I would have two tables

1. Clients table - stores all the meta data related to a client (name, description, etc) and a unique, auto-incrementing ID column
2. Jobs table - stores all the meta data related to a job. Additionally, it should have column (clientId) that can act as a foreign key to the clients table. The value of this column would always be the value of the id column of the client the job is for

So, this would just return clients

SELECT * FROM clients

This would get all jobs for a client

SELECT * FROM jobs WHERE clientId = xxx

[justsomeguy]

If you're keeping track of clients then you should have a table that lists clients separately from jobs. So, a clients table and a jobs table where the job has a field for the client ID that it belongs to. Then your dropdown would select from the clients table.If you want unique values from a field then you can use the distinct keyword.SELECT DISTINCT client FROM jobs

[Steven]

Alright, that makes a lot of sense. I figured I had to change my database somehow.

 

Right now the page where users input job log details, the form uses action="insert.php", I'm guessing with two tables (clients and jobs) I'd have two insert pages (something like insertClient.php and insertJob.php) that would send the inputed data to the correct tables?

[justsomeguy]

You would have separate pages for managing customers and jobs. When you're adding a new job, you might go to the page that displays that customer and click a link to add a new job for the customer. Or a form to add a new job where you select the customer from a dropdown. I guess you could also have a form where you add both a new customer and a new job for them.

[Steven]

Excellent, thank you.

 

Another follow-up question. After I made my separate Clients form and results page, I found that I was still able to add duplicates. I tried browsing Stackoverflow for an answer to avoiding duplicating, but couldn't find much help. Then I went into my phpmyadmin page and set the 'name' column to UNIQUE and it works. I tested adding a duplicate client and received an error saying there was a duplicate for "name". That's good, that's what I want, but how can I control that error message? Mainly for UX reasons, I want to control that error message so I can style it as well as give the user a helpful link back to the form.

 

 

Many thanks

[thescientist]

what's your code so far?

[Steven]

	$sql="INSERT INTO clients (name)		VALUES 		('$_POST[name]')";		if (!mysqli_query($con,$sql))		{			die('Error: ' . mysqli_error($con));		}		echo "1 record added";		mysqli_close($con);?>

I haven't put in any code to show an error on duplication, not sure how to go about that. But MySQL is apparently reporting its own error on the browser after I set the "name" column to UNIQUE.

[thescientist]

try the answer here

http://stackoverflow.com/questions/3146838/handle-error-for-duplicate-entries-php-mysql

[Steven]

Hmm, that answer looked promising. It's still giving me the default error, though.

 

Here's my code:

$sql="INSERT INTO clients (name)	VALUES 	('$_POST[name]')";	if (mysql_errno() == 1062) {		echo "That client already exists!";	}			if (!mysqli_query($con,$sql)) {		die('Error: ' . mysqli_error($con));	}	echo "1 record added";	mysqli_close($con);?>

[Steven]

Okay, I think my problem is that answer is using mysql rather than mysqli. I'm browsing the PHP manual for mysqli_error but haven't found anything real helpful yet.

[justsomeguy]

	if (mysql_errno() == 1062) {		echo "That client already exists!";	}			if (!mysqli_query($con,$sql)) {		die('Error: ' . mysqli_error($con));	}

You're checking for the error condition before you even run the query. You're also mixing mysql and mysqli.

[Steven]

Whoops.

 

After I sent it down it's still just returning the default error.

 

Edit:

 

This is interesting. I tried removing the following code:

if (!mysqli_query($con,$sql))	{		die('Error: ' . mysqli_error($con));	}

Making the code in full:

<?php	$con=mysqli_connect("host","user","pass","db");	// Check connection	if (mysqli_connect_errno())		{			echo "Failed to connect to MySQL: " . mysqli_connect_errno();		}	$sql="INSERT INTO clients (name)		VALUES 		('$_POST[name]')";		if (mysql_errno() == 1062) {			print "That client already exists!";		}		echo "1 record added";		mysqli_close($con);?>

I'm not getting this error message anymore: "error Duplicate entry for key". It's echoing "1 record added", ignoring the if mysql_errno statement. I looked at the DB itself, and it isn't making any duplicates, but I'm wondering why it isn't giving the error report I want it to?

 

 

Thanks again for your help and patience, guys.

Edited March 14, 2014 by Steven

[thescientist]

it should do one or the other

 

 

$sql="INSERT INTO clients (name) VALUES ('$_POST[name]')";if (mysql_errno() == 1062) {  print "That client already exists!";}else{  echo "1 record added";}

[Steven]

Okay, another update.

 

I went to the PHP Manual (RTF, amirite?). My suspicion, though I could be wrong, is that trying to use mysql while the rest of my code is using mysqli is causing some hangup. So I used this piece from the manual to find the error number occuring for mysqli:

if (!mysqli_query($con, "SET a=1")) {  printf("Errorcode: %dn", mysqli_errno($con));}

That gave me an error number of 1193, so then I tweaked the piece of code I got from the Stackoverflow answer from earlier and came up with this:

if (!mysqli_errno() == 1193) {  print "That client already exists!";}

It's working! Sort of... It does indeed print "That client already exists", but only after reporting "1 record added" and an error message:

Warning: mysqli_errno() expects parameter 1 to be mysqli, string given in ..page.php on line 30

I also tried changing "if (!mysqli_errno() == 1193" to "if (!mysqli_errno($con) == 1193"

[Steven]

*facepalm*

 

Yes, that's right

[Steven]

I'm getting a strange error.

 

I'm trying to add new clients, but it has been telling me the client already exists. I thought maybe the autoincrement was acting weird, so I dropped the table and started fresh. Still said the client already exists.

 

I tried using the mysqli error report from the PHP manual, but I'm assuming I'm doing something wrong, because all it reports is:

Errormessage: Unknown system variable 'a'

Here's my current code:

<?php	$con=mysqli_connect("host","user","pass","dbname");	// Check connection	if (mysqli_connect_errno()) {			printf("Connect failed: %sn", mysqli_connect_error());			exit();		}	$sql = "INSERT INTO clients (name) VALUES ('$_POST[name]')";	if (!mysqli_query($con, "SET a=1")) {		printf("Errormessage: %sn", mysqli_error($con));	}	else {		echo "Great! We've successfully added a new client.";	}		mysqli_close($con);?>

[Steven]

I found a better error report snippet. It's giving me useful information, but not sure how to proceed. Here's my current code:

<?php  mysqli_report(MYSQLI_REPORT_OFF); // Turn off default error msgs  $mysqli = new mysqli("host", "user", "pass", "dbname");  $query = "INSERT INTO clients (name) VALUES ('$_POST[name]')";  $res = $mysqli->query($query);    if ($mysqli->error) {      try {        throw new Exception("MySQL error $mysqli->error <br> Query:<br>$query", $mysqli->errno);      }      catch(Exception $e) {        echo "Error No: ".$e->getCode(). " - ". $e->getMessage() . "<br>";        echo nl2br($e->getTraceAsString());      }    }    else {      echo "Great! A new client has been added.";    }?>

And I just realized in the middle of typing this that when I reset the clients table, I forgot to set the client id column to auto-increment. Fixed.

[Steven]

Okay, yet another followup question. I'm guessing I have a sql "architecture" problem.

 

So I have separate tables for clients and jobs. I have successful pages (resultsClients.php and resultsJobs.php) that display all entries for those two tables. And, I've successfully made it so that, for example, on the resultsClients.php page you can click a client's name and it will bring you to viewclient.php and will display that client's name and details. I also have done that for the resultsJobs.php page where you can click on any of the jobs' unique job number link and it will bring you to viewjob.php and it will show you that particular job's detailed information.

 

Going great so far.

 

But, on the resultsJobs.php page, I want the name of the client for whom each job is for to be linked to viewclient.php as well. Problem is, after I make the link, each client on resultsJobs.php has a clientid of 1, and not its unique id that it is supposed to have.

 

I think my real question is: when the user fills out the job form page, how do I link the client text field with the client table? Code is below:

 

Here's the form from "formJobs.php"

<div class="formContainer">	<form action="insertJobs.php" method="post">	<p>		<label>Client:</label> 		<input type="text" name="client" class="focus-glow job--clientInput auto" size="28">                <span class="job--clientHint">Enter the client name</span>    <br>				<label>Job Number:</label> 		<input type="text" name="jobnumber" class="focus-glow job--jobnumberInput" size="28">		<span class="job--jobnumberHint">DSI job number</span><br>		<label>Status:</label> 		<input type="text" name="status" class="focus-glow job--statusInput" size="28">		<span class="job--statusHint">Delivery status</span><br>		<label>Date Received:</label> 		<input type="date" name="datein" class="focus-glow datepicker job--dateinInput" size="28">    <span class="job--dateinHint">Date placed</span><br>				<label>Date Due:</label> 		<input type="date" name="datedue" class="focus-glow datepicker job--datebyInput" size="28">    <span class="job--datebyHint">Date due, if any</span><br>				<label>Date Out:</label> 		<input type="date" name="dateout" class="focus-glow datepicker job--dateoutInput" size="28">    <span class="job--dateoutHint">Date shipped</span><br>				<label>Completed:</label> 		<input type="text" name="completed" class="focus-glow job--completedInput" size="28">    <span class="job--completedHint">Yes / No</span><br>				<label>PO Number:</label> 		<input type="text" name="po" class="focus-glow job--ponumberInput" size="28">    <span class="job--ponumberHint">Client PO, if any</span><br>				<label>Description:</label> 		<textarea type="text" name="description" class="focus-glow"  size="28"></textarea>		<p>		<input type="submit" label="Submit">		<p>	</form>	<p class="viewResults"><a href="resultsJobs.php">View results</a></p></div><!-- /form container -->

And "resultsJobs.php":

<?php require('includes/config.php'); ?><!doctype html><html lang="en"><head>  <meta charset="utf-8">  <title>Custom Project Management System</title>  <link rel="stylesheet" href="style.css"></head><body><div id="container">	<!-- Include Navigation -->	<?php include ('includes/header.php'); ?>	<!-- Include Navigation -->	<?php include ('includes/navigation.php'); ?><h2>Job Log</h2><?phpecho "<table><tr><th>Client</th><th>Job No.</th><th>Status</th><th>Date In</th><th>Date Due</th><th>Date Out</th><th>Completed</th><th>Description</th><th>Purchase Order</th></tr>";$result = mysqli_query($con,"SELECT * FROM jobs, clients GROUP BY jobnumber");while($row = mysqli_fetch_array($result)){	echo "<tr>";	echo '<td><a href="viewclient.php?clientid='.$row['clientid'].'">'.$row['name'].'</a></td>';	echo '<td><a href="viewjob.php?jobnumber='.$row['jobnumber'].'">'.$row['jobnumber'].'</a></td>';	echo "<td>" . $row['status'] . "</td>";	echo "<td>" . $row['datein'] . "</td>";	echo "<td>" . $row['datedue'] . "</td>";	echo "<td>" . $row['dateout'] . "</td>";	echo "<td>" . $row['completed'] . "</td>";	echo "<td>" . $row['description'] . "</td>";	echo "<td>" . $row['po'] . "</td>";	echo "</tr>";}echo "</table>";?></div><!-- / main container --></body></html>

[justsomeguy]

You're joining the jobs and clients tables, but you're not telling it how to join them. The jobs table should have an ID referring to an entry in the clients table, that's what you should be joining the tables on. Without specifying how to join the tables, the result is every combination of rows between the two tables. The GROUP BY clause basically is having it filter out duplicate job numbers.

[Steven]

I've been doing some reading, and it sounds like I need to have a foreign key in the jobs table that connects to the "clientid" from the clients table. Does that sound right to you?

[Steven]

Ok, so I started out again with a separate, new database. Just to start fresh after trying out so many things. I'm having trouble understanding how to POST data to two related tables. Here are my tables:

Table: clients--------------------------|  id  (pk, ai) |  name  | --------------------------

Clients has two columns: "id" and "name". "id" is the primary key and it auto-increments. "name" is a simple varchar for the client's name.

Table: jobs--------------------------------------------------------------------------|  clientid (fk) |  clientname (fk) | jobid (u, ai) | date | description | --------------------------------------------------------------------------

The jobs table is the child of clients. "clientid" is a foreign key that points to clients.id, as well as "clientname" is a foreign key that points to clients.name. "jobid" is a unique, auto-incremented key and is used to distinguish all jobs from eachother. "date" and "description" are pretty simple.

 

On the page that has the form for submitting new jobs, I have fields for "Client Name", "Date" and "Description". In this case, I want the database to assign a unique number to the jobid (preferably a random 5-digit number, but that's not my concern right now). What I've been getting hung up on for quite some time now is the "Client Name" field. In the insert.php file, exactly how do I handle the relationship between the two tables in my INSERT command? I need a bit more help being walked through how to join the tables.

 

 

Thank you in advance!

[thescientist]

you need to know the client id ahead of time.

 

one way might be to have a dropdown of clients pulled from the database on the jobs page. A user selects the client name, and the _value_ passed along in $_POST would ideally be the clientid. So the options for a select tag would have the id assigned to the value attribute, and the display text would be the client name.

 

Then you can INSERT the clientId with the rest of the job data.

[justsomeguy]

Don't put the client name as a foreign key in the jobs table. Only use the client ID. Do not duplicate the data in the database, there's no reason to list the client name twice in different tables. If you update the client name you only want to do that in one place. The client ID is enough to link the job to a particular client.

[Steven]

Oi. Having a heck of a time with this. Here's the error:

Notice: Undefined index: clientid in C:xampphtdocsmos-loginsertJobs.php on line 14Notice: Undefined index: clientname in C:xampphtdocsmos-loginsertJobs.php on line 14Error: Cannot add or update a child row: a foreign key constraint fails (`moslog`.`jobs`, CONSTRAINT `jobs_ibfk_1` FOREIGN KEY (`clientid`) REFERENCES `clients` (`id`))

Here's my job form page:

<div class="formContainer">	<form action="insertJobs.php" method="post">	<p>		<label>Client:</label>		<?php			$result = mysqli_query($con,"SELECT * FROM clients");			echo "<select name='name'>";			while ($row = mysqli_fetch_array($result)) {				echo "<option value='" . $row['clientid'] . "'>" . $row['name'] . "</option>";			}			echo "</select>";    ?>    <br>		<label>Date Received:</label> 		<input type="date" name="datein" class="focus-glow datepicker job--dateinInput" size="28">    <span class="job--dateinHint">Date placed</span><br>				<label>Description:</label> 		<textarea type="text" name="description" class="focus-glow"  size="28"></textarea>				<p>				<input type="submit" label="Submit">				<p>	</form>

here's the job insert page:

<?php require('includes/config.php'); ?><!doctype html><html lang="en"><head>  <meta charset="utf-8">  <title>Custom Project Management System</title>  <link rel="stylesheet" href="style.css"></head><?php	$sql="INSERT INTO jobs (clientid, clientname, datein, description)		VALUES 		('$_POST[clientid]','$_POST[clientname]','$_POST[datein]','$_POST[description]')";		if (!mysqli_query($con,$sql))		{			die('Error: ' . mysqli_error($con));		}		echo "1 record added";		mysqli_close($con);?><p><a href="formJobs.php">Add another job</a></p><p><a href="resultsJobs.php">View results</a></p>

I'm coming to my wit's end =