Jump to content

PHP SQL drop-down question


Steven

Recommended Posts

So, as far as my understanding of what's going on in the <select> tag on my jobform page, it looks as though it's having a problem with <option value = "clientid">, as my $result is only selecting from the clients table, which only has columns "id" and "name". Am I supposed to be selecting both tables (clients and jobs)? It seems like I should be, so that <option value = "clientid"> actually knows what and where "clientid" actually is. If that's the case, what is the best way to SELECT those two tables?

 

And on my insert page, how am I supposed to tell it clientname = the name column from clients table pulled from the <select> option from the jobform page?

 

I'm trying to figure out how to think properly within PHP/MySQL and I'm failing hard! :)

Link to comment
Share on other sites

Try to add to insertJobs.php at line 1, the code below

<? die($_POST[clientid]) ?>

I guess it will be helpful to debug and find where is the problem, first of all, by checking your passed variables.

It seems you don't send any var named "clientid". your SELECT name is "name".

Edited by Yolo
Link to comment
Share on other sites

  • You have no input with the name "clientid"
  • Your select element name is "name", not "clientname" as your PHP is expecting

 

As JSG, suggested, you only need one foreign key in your jobs table. I would drop client name from the jobs table, and only have clientid as the foreign key to the clients table. It's not good to have data repeated in a database. That is one of the rules of normalization.

 

Then your form just has to submit clientid from the dropdown, and your two other fields, and just INSERT's those.

Link to comment
Share on other sites

Thanks guys,

 

I'm getting close. But I'm getting a duplication bug here.

 

Here's my formJobs.php:

<?php$result = mysqli_query($con,"SELECT * FROM clients");echo "<select name='clientid'>";while ($row = mysqli_fetch_array($result)) {    echo "<option value='" . $row['id'] . "'>" . $row['name'] . "</option>";}echo "</select>";?>

Here's insertJobs.php:

	$sql="INSERT INTO jobs (clientid, datein, description)		VALUES 		('$_POST[clientid]','$_POST[datein]','$_POST[description]')";		if (!mysqli_query($con,$sql))		{			die('Error: ' . mysqli_error($con));		}		echo "1 record added";

And here's resultsJobs.php:

echo "<table><tr><th>Job ID</th><th>Client ID</th><th>Client</th><th>Date</th><th>Description</th></tr>";$result = mysqli_query($con,"SELECT jobs.*, clients.* FROM jobs JOIN clients");while($row = mysqli_fetch_array($result)){    echo "<tr>";    echo "<td>" . $row['jobid'] . "</td>";    echo "<td>" . $row['clientid'] "</td>";    echo "<td>" . $row['name'] . "</td>";    echo "<td>" . $row['datein'] . "</td>";    echo "<td>" . $row['description'] . "</td>";    echo "</tr>";}echo "</table>";

I'm not getting any error messages, but I'm missing something. When I view my results page, it shows me two rows in my table I told it to feed the results to, it looks like this:

-------------------------------------------------------------------------|  Job ID  |  Client ID  |  Client       |  Date        |  Description  |-------------------------------------------------------------------------|  12      |  2          |  "client.02"  |  2014/03/20  |  abc          ||  12      |  2          |  "client.01"  |  2014/03/20  |  abc          |-------------------------------------------------------------------------

Now, it's correctly reporting that the job I just submitted "job #12" belongs to "client #2", but I'm having trouble figuring out why it is duplicating the job for every client. It says "job #12" also belongs to client #1, and is reporting client #1 as having id #2...

Link to comment
Share on other sites

I think I fixed it...

 

I changed this snippet in resultsJobs.php:

$result = mysqli_query($con,"SELECT jobs.*, clients.* FROM jobs JOIN clients");

to...:

$result = mysqli_query($con,"SELECT jobs.*, clients.* FROM jobs JOIN clients WHERE clientid=id");

Now, it appears as though it is doing what I want, but is that the correct way to do it? Just want to make sure I'm doing this right :)

 

 

 

--- EDIT --------

 

 

I take that back. I just attempted to submit a second job for "client 02", but it spits this back at me:

 

"Error: Duplicate entry '2' for key 'PRIMARY'" In the clients table, the id is unique, and should not be duplicated. But in the jobs table the clientid should be allowed to duplicated, as clients have multiple job orders... Is this possible with foreign keys? =

Edited by Steven
Link to comment
Share on other sites

That's what happens when you join 2 tables without any conditions, the result is every possible combination of rows. You need to tell it how to join the rows, i.e. only join rows where the client ID in the jobs table matches the ID in the client table.Also, you need to start using prepared statements any time you're using data in a query. This is dangerous:

	$sql="INSERT INTO jobs (clientid, datein, description)		VALUES 		('$_POST[clientid]','$_POST[datein]','$_POST[description]')";
You need to prepare the statement first, with placeholders for the values. Then you add the values, then execute the query.http://www.php.net/manual/en/mysqli.prepare.phphttp://www.php.net/manual/en/mysqli-stmt.bind-param.phphttp://www.php.net/manual/en/mysqli-stmt.execute.php
$stmt = mysqli_prepare($con, "INSERT INTO jobs (clientid, datein, description) VALUES (?,?,?)");mysqli_stmt_bind_param($stmt, 'sss', $_POST['clientid'], $_POST['datein'], $_POST['description']);$success = mysqli_stmt_execute($stmt);if (!$success) {  echo 'Database error: ' . mysqli_stmt_error($stmt);}
That's the way to do it with procedural code. Object-oriented code would look a little cleaner.
Link to comment
Share on other sites

I think it is for the jobs table, and clientid column. the primary key for the jobs table is clientid. Looking at it, I'm not sure why I did that, jobid should be the primary key, shouldn't it? In any case, clientid is the primary key for jobs table.

Link to comment
Share on other sites

I looked at the indices in PMA and it looked like there was some duplicate key on jobid_2. I removed that, tried again, and now it's able to send more jobs. I wonder how that was interfering?

Link to comment
Share on other sites

I think I fixed it.

 

I bought "PHP and MySQL" by Larry Ullman and a similar O'Reilly book on Amazon and they came yesterday. Larry Ullman's book is paying off already. I snuck ahead and used a snippet of his and came up with this:

$result = mysqli_query($con,	"SELECT j.*, c.* FROM jobs AS j INNER JOIN clients AS c ON j.clientid = c.id WHERE j.jobid = $id");

I altered this a little bit for the viewclients page, and it worked there as well. No duplicates. I also realized, at least on the viewclients page, I was getting the client name constantly duplicated, because I had the name included in the while loop with all the jobs. So, I did this:

		$id = $_GET['clientid'];		$name = mysqli_query($con, "SELECT * FROM clients WHERE id=$id");				while($row = mysqli_fetch_array($name))		{			echo '<h2 class="h2--viewClient">'.$row['name'].'</h2>';		}		$result = mysqli_query($con,			"SELECT j.*, c.* FROM jobs AS j INNER JOIN clients AS c ON j.clientid = c.id WHERE c.id = $id");		while($row = mysqli_fetch_array($result))		{			echo '<p>Job ID: '.$row['jobid'].'</p>';			echo '<ul>';				echo '<li>Date Ordered: '.$row['datein'].'</li>';				echo '<li>'.$row['description'].'</li>';			echo '</ul>';		}

It's working great, but is that the correct way to do that? Nevermind normalization and security -- for now. I'll be revisiting all this once I cover that chapter in Ullman's book and have a firmer understanding.

 

Thanks for all the help so far.

Link to comment
Share on other sites

I have a follow-up question.

 

on the jobsform page, we switched it to a dropdown list so we could have the client id available. Previously, while still using a text field to fill in the client name, I used jQuery Autocomplete to suggest client names from the client table as the user was typing. Now, as more users get added, a dropdown method will become unruly. Is there a way to use the jQuery Autocomplete method to perform the same way?

Link to comment
Share on other sites

Nevermind normalization and security -- for now.

I am not a fan of that way of thinking. Security is not an afterthought, you design it in from the beginning. You can either take the time to do it right, or make the time to do it over. Right now, you need to do that code over because it's not secure. If you were using prepared statements then you would not need to do it over.

on the jobsform page, we switched it to a dropdown list so we could have the client id available. Previously, while still using a text field to fill in the client name, I used jQuery Autocomplete to suggest client names from the client table as the user was typing. Now, as more users get added, a dropdown method will become unruly. Is there a way to use the jQuery Autocomplete method to perform the same way?

I imagine that jQuery has a component that acts like a dropdown that you can type in to filter, like the ones here from ExtJS:http://dev.sencha.com/deploy/ext-4.0.0/examples/form/combos.html
Link to comment
Share on other sites

Are prepared statements only used in conditional SQL queries?

 

For example, say I have a simple query: SELECT * FROM clients, does that need prepared statements? I tried to give it one, but it's erring out on me. Here's my code:

	echo "<table>";	echo "<tr>";	echo "<th>Client</th>";	echo "</tr>";	$q = "SELECT * FROM clients WHERE name=?";	$stmt = mysqli_prepare($con,$q);	mysqli_stmt_bind_param($stmt, 's', $name);	$name = $row['name'];	mysqli_stmt_execute($stmt);	echo "<tr>";	echo "<td>"."$name"."</td>";	echo "</tr>";	echo "</table>";

The "$name = $row['name'];" doesn't seem right to me, but how else would I grab the actual value for $name?

 

Or, as I originally asked, are prepared statements only used on conditional statements? That'd seem a little odd if that were the case.

Edited by Steven
Link to comment
Share on other sites

I'm not sure how that would work if $name is undefined when you pass it to bind param. You can just pass $row['name'], that will work. Or define $name first so that at least it exists. Using bind param will pass the variable by reference, so that if you change the value after using bind param then the query will still use the new value, but I'm not sure if references work when the variable is undefined to start with.

Link to comment
Share on other sites

I tried this:

	$q = "SELECT * FROM clients WHERE name=?";	$stmt = mysqli_prepare($con,$q);	mysqli_stmt_bind_param($stmt, 's', $row['name']);	mysqli_stmt_execute($stmt);	echo "<tr>";	echo "<td>".$row['name']."</td>";	echo "</tr>";	echo "</table>"; 

But it isn't returning anything. It isn't showing errors, displays the page but no info from the table.

Link to comment
Share on other sites

$row doesn't have a value there.One way is to use get_result after execute, and then you can loop over that with a fetch function, check the examples here:http://www.php.net/manual/en/mysqli-stmt.get-result.phpIf you're only selecting certain columns, then you can also use bind_result and then fetch:http://www.php.net/manual/en/mysqli-stmt.fetch.php

Link to comment
Share on other sites

Oh, sure. That makes sense.

 

I'm doing some reading on the links you gave me, and looked into bind_param and bind_results a bit more. Now, correct me if I'm wrong, but it looks like bind_param is used for sending input to the database, but bind_results is for getting results from the database to print on your page. That must be part of my problem as well, as I've been trying to get bind_param to work when I'm trying to fetch results from my query.

Link to comment
Share on other sites

Alright, with the help of those links, I got it working fairly well:

<?php	echo "<table>";	echo "<tr>";	echo "<th>ID</th>";	echo "<th>Client</th>";	echo "</tr>";	// prepare statement	if ($stmt = mysqli_prepare($con, "SELECT * FROM clients ORDER BY id")) {		mysqli_stmt_execute($stmt);		// bind variables to prepared statement		mysqli_stmt_bind_result($stmt, $id, $name);		// fetch values		while (mysqli_stmt_fetch($stmt)) {			echo "<tr>";			echo "<td>$id</td>";			echo "<td>$name</td>";			echo "</tr>";			echo "</table>";		}		// close statement		mysqli_stmt_close($stmt);	}?>

Only problem is, it displays only the first result inside the table. The rest of them look as though they are outside the table. I tried browsing StackOverflow and came across this one. But the one answer makes an odd suggestion:

while ($row = mysqli_fetch_array($stmt)) {    echo "<tr>";    echo "<td>" . $row["aid"] . "</td>";    echo "<td>" . $row["aname"] . "</td>";    echo "</tr>";}

To me, that seems to defeat the purpose of binding the results...

 

---- EDIT -------

 

Okay, I made a newby mistake. Read up on the "while" statement, and realized I had included echo "</table>"; which was causing the table to close after each iteration. I moved that outside of the while loop, and it's working. :)

Edited by Steven
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...