PHP SQL Combo box how to grab all variables

[jgreen]

I have 3 variables coming from 3 columns from a table.  I want to insert these variables into 3 columns in a different table.  

 

Variables are name,  cover, and pageno.   

 

The issue is I can see the values in the hidden inputs this is when the page loads when nothing is selected in the combobox???    If I select book number two the same values are in the hidden inputs.  

If I select book one this is what I see because when the page loads it populates the values as book 1.    
<input type="hidden" name="cover" value="cover 1">
<input type="hidden" name="pageno" value="pageno 1"> 

If I select book two this is what I see.
<input type="hidden" name="cover" value="cover 1">
<input type="hidden" name="pageno" value="pageno 1"> 

Make sense?  If not, no matter which book I select I see the same values.   Here's the code.  

<select name="name">
<option value="<?php echo "{$_POST['name']}"; ?>"> </option>                      
       
<?php
include('theconnection.php');
$con = mysqli_connect($host,$user,$pass,$dbName);
if (!$con)
{
die('cannot connect: ' . mysqli_error($con));
}
mysqli_select_db($con,"thebooks");
$result = mysqli_query($con,"SELECT * FROM books");
$result = mysqli_query($con,"SELECT b.id, b.name, b.cover, b.pageno FROM books");

$cover = '';
$pageno = '';

while($row = mysqli_fetch_array($result))
{
echo ("<option value='$row[name]'>$row[name] $row[cover], $row[pageno] </option>");

$cover = "$row[cover]";
$pageno = "$row[pageno]";

}

?>
                      
</select>

<input type="hidden" name="cover" value="<?php echo $cover; ?>">
<input type="hidden" name="pageno" value="<?php echo $pageno; ?>">

Maybe Javascript will work?  I can't figure out how to do this with Javascript either.   

Edited August 16, 2015 by jgreen

[Ch0cu3r]

 

 

The issue is I can see the values in the hidden inputs this is when the page loads when nothing is selected in the combobox???    If I select book number two the same values are in the hidden inputs. 

If I select book one this is what I see because when the page loads it populates the values as book 1.   

<input type="hidden" name="cover" value="cover 1">

<input type="hidden" name="pageno" value="pageno 1">

If I select book two this is what I see.

<input type="hidden" name="cover" value="cover 1">

<input type="hidden" name="pageno" value="pageno 1">

Make sense?

Yes. PHP cannot act on events happening in the browser. PHP only runs when a (HTTP) request is made. PHP is a server side language.

 

If you want the hidden input fields populated when you select an option from your dropdown menu then you need to use javascript.

 

Also your code will always populate the hidden input fields with last row returned by you query.

 

Can I ask what it is you are trying to?

[jgreen]

I have a form and I'm populating the combo box as you see in my code.  I'm populating the combo box from a table called books.  

The form populates a table called users.  Each user will select a book and that table gets populated with user info plus the book name, cover, and pageno.  

 

Since the combo box name is "name", I can only post the book name.  

This is why I have two hidden fields that I'm trying to populate with the other variables cover and pageno.  

 

Does anyone have a javascript solution?  

 

I tried this code below but again only get name since the id is on the select.... not sure how to change it to get all variables -  name, cover, pageno

 

<script type='text/javascript'>
$(function() {
$('#myselect').change(function() {

var x = $(this).val();

$('#myhidden').val(x);
});
});
</script>

<input type='hidden' id='myhidden' value=''>

[Ch0cu3r]

You should only have the combo box submit the book id. On the page where the form is submitted to. you will query the books table to get the book name, cover and pagno where the book id matches.

[Barand]

You should only have the combo box submit the book id. On the page where the form is submitted to. you will query the books table to get the book name, cover and pagno where the book id matches.

 

exactly what I told the OP five days ago

 

http://forums.phpfreaks.com/topic/297741-post-variables-from-a-while-loop-from-a-second-table-in-form/?do=findComment&comment=1518615

[jgreen]

Barand, My apologizes someone posted after you and I didn't see it.  Looking at this on my phone.  I added your code

SELECT b.book_id, b.name, b.cover, b.pageno, b.booktotal FROM books b LEFT JOIN users u USING (name) GROUP BY b.name HAVING COUNT(u.name) < b.booktotal WHERE b.book_id = $posted_id

But it doesn't populate the db.  When I remove your code 

WHERE b.book_id = $posted_id

it populates.  Can you tell me what this issue is?  Thanks

Edited August 17, 2015 by jgreen

[Barand]

The query I gave you in a previous post was to populate your dropdown menu. (http://forums.phpfreaks.com/topic/297658-how-to-select-two-tables-and-compare-two-columns-and-determine-or/?do=findComment&comment=1518346)

 

It did NOT contain "WHERE b.book_id = $posted_id".

 

You seem to be confusing issues. The WHERE clause is for selecting the selected book from you database on the subsequent page.

[jgreen]

No I'm not confusing the issues.  I have the code right here in front of me and know how this form works.

 

This is the same combobox AND you didn't give me that code to populate the dropdown, I already had that done.  What you did though is select the data and determine how many records exist.  If those records match the fulfilled amount then not to display those book names in the combobox.  

 

This is the same combobox... and the other issue after you fixed the first one is to populate the second table with the other entries.  And trying to find a solution on how to do that.  

It did NOT contain "WHERE b.book_id = $posted_id".

No kidding... this is what you posted for the select so I added it.  Where does this code go?   If you're saying put it under the insert at the top of the page that will NOT work.    

[jgreen]

Also there isn't a subsequent page so seems you're confusing me with another.  It's a form and after submit goes directly into a db.  

[Ch0cu3r]

No you are misunderstanding Barand

 

1. Your combobox should only be submitting the book id (not the book name). No need for the hidden input fields either.

 

2. The query Barand gave in this post is what you need to insert the book id, name, cover and pageno into your table. (Needs to be executed as one complete statement, not separately).  $posted_id is the variable that contains the book id submitted by your combobox when the form is submitted (looking at your form code this will be $_POST['name']  - altough your should rename your combobox to bookid then use $_POST['bookid']) - this is what Barand meant by subsequent page

[jgreen]

Ch0cu3r, Thanks for explaining.  I've done what both of you have described.  

 

This is the reason I said this earlier "If you're saying put it under the insert at the top of the page that will NOT work."  

 

The reason I say, "This will not work" is because of this error -  "Column count doesn't match value count at row 1".  It says this because the insert has more columns than the select. 

This keeps getting skipped over.  

 

The form has more than a combo box.  I mean really who would have a combo box only and a submit button... Even if I did not say there's more on the form, it would only make sense.  It wouldn't make sense to have 2 tables with the same data.  

 

The form also has First Name, Last Name, email, etc. that gets inserted into table 2.   There's also, as you know - bookid, name, cover, pageno coming from the combo box.  

 

If I remove First Name, Last Name, email, etc. Barand's code works but only if the columns are =.    

 

There are 2 tables table1 (books)  &  table2 (users)< this table needs the book selected combined with the user info.  

 

I'm sure there's a way to do this but not sure how the sql should be written.   

 

Again thank you for going into detail explaining what Barand was saying, it helped a lot.  

 

It's too bad your not understanding it's bigger than a combo box and a submit button.

 

If I'm wrong for thinking this please explain.  Tell me what the issue is since it seems you can explain things in detail so people can understand.  

 

 

Here's the code  - 

INSERT INTO table2 (firstname, lastname, address, city, state, zipcode, email, book_id, name, cover, pageno)
SELECT book_id, name, cover, pageno
FROM table1
WHERE book_id = $posted_id

Table 1  -  book_id, name, cover, pageno, bookcopies

 

Table 2  -  firstname, lastname, address, city, state, zipcode, email, book_id, name, cover, pageno

Edited August 18, 2015 by jgreen

[Ch0cu3r]

 

The form also has First Name, Last Name, email, etc. that gets inserted into table 2.   There's also, as you know - bookid, name, cover, pageno coming from the combo box.

You only told use about the latter and that is what Barand's INTO SELECT query is for. We can only suggest what you tell us, if you told us about the other fields we would of suggested something different

 

Now that we know this we can move on to a different solution.

 

As you have the book cover, name and pageno as hidden input fields I assume the user is not going to be editting this data? If so then there is no need to submit this.

Therefor we do not insert the book name, cover and pageno in your second table. The only value that needs to be inserted is the book id (which I assume is used for assigned the book to the user?). Data should be duplicated across different tables.

 

Data stored in the tables should relate (MySQL is a relational database). So the book id should be used as foreign key

 

You can now use a regular insert query to insert your form data into the second table

INSERT INTO table2 (firstname, lastname, address, city, state, zipcode, email, book_id) 
VALUES ('$firstname', '$lastname', '$address', ... etc, $book_id)

On the pages where you are outputting the contents of table2 to get the book name, cover and pagno would you use a JOIN

SELECT t2.firstname, t2.lastname, t2.book_id,  # select these columns from table 2
       t1.name, t1.cover. t1.pageno            # select these column from table1 (books)
FROM table2 t2
LEFT JOIN table1 t1 USING(book_id)             # join the tables where the book id matches in both tables 

Example

> SELECT * FROM table1
+-----------+----------+---------+
| firstname | lastname | book_id |
+-----------+----------+---------+
| John      | Doe      | 1       |
| Tom       | Wood     | NULL    | # person does not have a book
| Clair     | Watts    | 2       |
| Sam       | Taylor   | 1       |
| John      | Doe      | 3       |
+-----------+----------+---------+

> SELECT * FROM table2
+---------+-----------------------------------+------------+--------+
| book_id |               name                |   cover    | pageno |
+---------+-----------------------------------+------------+--------+
| 1       | Charlie and the Chocolate Factory | Roald Dahl | 208    |
| 2       | James and the Giant Peach         | Roald Dahl | 160    |
| 3       | The Twits                         | Roald Dahl | 112    |
+---------+-----------------------------------+------------+--------+

> SELECT t1.firstname, t1.lastname, t1.book_id,
         t2.name, t2.cover, t2.pageno           
  FROM table1 t1
  LEFT JOIN table2 t2 USING(book_id) 
+-----------+----------+---------+-----------------------------------+------------+--------+
| firstname | lastname | book_id |               name                |   cover    | pageno |
+-----------+----------+---------+-----------------------------------+------------+--------+
| John      | Doe      | 1       | Charlie and the Chocolate Factory | Roald Dahl | 208    |
| Tom       | Wood     | NULL    | NULL                              | NULL       | NULL   | # null because person has no book
| Clair     | Watts    | 2       | James and the Giant Peach         | Roald Dahl | 160    |
| Sam       | Taylor   | 1       | Charlie and the Chocolate Factory | Roald Dahl | 208    |
| John      | Doe      | 3       | The Twits                         | Roald Dahl | 112    |
+-----------+----------+---------+-----------------------------------+------------+--------+

[jgreen]

Thank you very much for the information.  I hope you're coding plus teaching people you have that ability to do so.   

The hidden fields -  No the users cannot edit those fields but I was using those fields to post so I can also populate an email with those values.  The email is sent when submitted.  I tried what you posted and made a little head way just trying to figure out how to hold on to those variables in order to populate the email. On the other side, linking the tables would be great.  I'll work through your suggestions more and let you know.  Thanks again.  

Edited August 19, 2015 by jgreen

[jgreen]

Ch0cu3r,  Thanks for the help but didn't actually solve the issue.  But it was a great answer for someone with a somewhat similar problem.