Inserting infomation into mysql

Protype

I got this message but am looking to find out how it is done can anyone help

"Hi,

I am assuming that you are facing problem inserting multiple selection in a table.

When you select more than one selection from drop down box, you have to insert those many rows.

Example: 2 selection 2 rows to be inserted.

Regards,"

Adam

what do yu mean you got that message? from where?

Protype

Mirror wrote: »

what do yu mean you got that message? from where?

From a forum but they are about as helpful as an underwater toaster

I find the people here are more helpful with their answers

Can you help

Adam

Protype wrote: »

From a forum but they are about as helpful as an underwater toaster

I find the people here are more helpful with their answers

Can you help

this may be true, but we cant help you up from 0 to done, you won't learn that way. have you made an attempt at it yet?

Protype

Mirror wrote: »

this may be true, but we cant help you up from 0 to done, you won't learn that way. have you made an attempt at it yet?

O ye lots of work. i was trying to use array. But this is a no no

What i need to know is from a dropdown, more than one selection can be made. Then going by what is above i should insert into two rows or as many as i need.

But how is this achieved if the selection is from one dropdown.
say i have a rep who works in ten counties and not just one

P.S. just one county works fine

Eoin

I think you will have to loop through all the selected values in the dropdown list and do an insert statement for each one (presuming that you are using a look up table linking the user id to a county ID).

I am fairly sure that you will get a comma delimited list of counties back if you select more than one, so you will need to split the string into an array, using the comma as the split character.

So, it will be something like this pseudo-code:

strSelect = Request.Form("yourdropdown")

if strselect contains a comma then

    arrayString = split the strSelect by comma

    for each value in the arrayString
        theCountyID = arrayString[i]
        sql = "insert into tblCountyUserLookup(userID, countyID) values(theUserID, theCountyID)"
       insert SQL
    end for loop
end if

First thing to do is just print out the value of the selected items in the dropdown list and see what you need to split them up into their own values.

Adam

being able to make more than one selection from a drop down is generally not the norm, as you would have to mark each one as it was selected, to be remembered for the final submission. you should consider using another method, such as checkboxes.

Protype

eoin wrote: »

I think you will have to loop through all the selected values in the dropdown list and do an insert statement for each one (presuming that you are using a look up table linking the user id to a county ID).

I am fairly sure that you will get a comma delimited list of counties back if you select more than one, so you will need to split the string into an array, using the comma as the split character.

So, it will be something like this pseudo-code:

strSelect = Request.Form("yourdropdown")

if strselect contains a comma then

    arrayString = split the strSelect by comma

    for each value in the arrayString
        theCountyID = arrayString[i]
        sql = "insert into tblCountyUserLookup(userID, countyID) values(theUserID, theCountyID)"
       insert SQL
    end for loop
end if

First thing to do is just print out the value of the selected items in the dropdown list and see what you need to split them up into their own values.

This is what i have should i change this? cos im not sure but trying

<select name="Instructor_Qual_id" size="4" multiple="multiple" id="Instructor_Qual_id">
                <?php
do {  
?>
                <option value="<?php echo $row_rsQual['Instructor_Qual_id']?>"><?php echo $row_rsQual['Instructor_Qualifacations']?></option>
                <?php
} while ($row_rsQual = mysql_fetch_assoc($rsQual));
  $rows = mysql_num_rows($rsQual);
  if($rows > 0) {
      mysql_data_seek($rsQual, 0);
      $row_rsQual = mysql_fetch_assoc($rsQual);
  }
?>
              </select>

Eoin

It's probably a bit cleaner than having one checkbox for each county, especially if you are catering for 26 32 over 25 counties.

Protype

Mirror wrote: »

being able to make more than one selection from a drop down is generally not the norm, as you would have to mark each one as it was selected, to be remembered for the final submission. you should consider using another method, such as checkboxes.

yes

would this be better

Protype

eoin wrote: »

It's probably a bit cleaner than having one checkbox for each county, especially if you are catering for 26 32 over 25 counties.

ye one of the boxes has 53

Eoin

Protype wrote: »

This is what i have should i change this? cos im not sure but trying

<select name="Instructor_Qual_id" size="4" multiple="multiple" id="Instructor_Qual_id">
                <?php
do {  
?>
                <option value="<?php echo $row_rsQual['Instructor_Qual_id']?>"><?php echo $row_rsQual['Instructor_Qualifacations']?></option>
                <?php
} while ($row_rsQual = mysql_fetch_assoc($rsQual));
  $rows = mysql_num_rows($rsQual);
  if($rows > 0) {
      mysql_data_seek($rsQual, 0);
      $row_rsQual = mysql_fetch_assoc($rsQual);
  }
?>
              </select>

I don't know what that code is trying to do as I know zero PHP.

Just create a page with a form containing a dropdown that you can select more than one value from. When you submit the form write out the value of the selected values to the screen. Each value will need its own SQL insert, so just work out how the values are separated - e.g. a comma.

Adam

doing things sort of backwards there... just a while statement is enough.

this is the sort of thing you're going for:

[php]
<?
$query="SQL QUERY";
$rsQual=mysql_query($query) or die(mysql_error());
?>


<select name="Instructor_Qual_id" size="4" multiple="multiple" id="Instructor_Qual_id">
<? while ($row_rsQual = mysql_fetch_assoc($rsQual)) { ?>
<option value="<?php echo $row_rsQual?>"><?php echo $row_rsQual?></option>
<? } ?>
</select>
[/php]

Protype

Just create a page with a form containing a dropdown that you can select more than one value from. When you submit the form write out the value of the selected values to the screen. Each value will need its own SQL insert, so just work out how the values are separated - e.g. a comma.

would i be right to say that each value is then sent to one field in the child table

So this is down to how i write the INSERT SQL

Adam

Protype wrote: »

would i right to say that each value is then sent to one field in the child table

So this is down to how i write the INSERT SQL

i'm confused as to what you actually need to do...the code you showed us implies your trying to populate a drop down box with values from a mysql query. but does the user need to be able to select more than one option from the dropdown??

Eoin

Protype wrote: »

would i be right to say that each value is then sent to one field in the child table

So this is down to how i write the INSERT SQL

You have a table that links a userID with a countyID, right? So, for each county a user is assigned to, there is a row in the database.

If that's the case, then you will need an INSERT statement for each selected value.

Protype

Mirror wrote: »

i'm confused as to what you actually need to do...the code you showed us implies your trying to populate a drop down box with values from a mysql query. but does the user need to be able to select more than one option from the dropdown??

The dropdown boxes are populated by the parent table in the database. so if i change a name in the database it will change across the site

SORRY this form is for me to insert new people and their information, so its a CMS for the database

Noting to do with the user

Adam

Protype wrote: »

The dropdown boxes are populated by the parent table in the database. so if i change a name in the database it will change across the site

yes...was that a question or a statement?

Eoin

Protype, I was going on your example of:

say i have a rep who works in ten counties and not just one

Can you say in English what you want to do, and then the code will follow easier.

Protype

Mirror wrote: »

yes...was that a question or a statement?

Sorry statement

Adam

Protype wrote: »

The dropdown boxes are populated by the parent table in the database. so if i change a name in the database it will change across the site

SORRY this form is for me to insert new people and their information, so its a CMS for the database

Noting to do with the user

thats irrelevant, if you're using it, then you are the user

Protype

eoin wrote: »

Protype, I was going on your example of:


Can you say in English what you want to do, and then the code will follow easier.

OK Sorry

Its a form for me to send information to the database.
The only thing i cant get to work is!
if i have a customer who works in many places/counties. i will need to store all the places of work in the database

The counties is a dropdown box
So i hope you can understand.

Protype

Mirror wrote: »

thats irrelevant, if you're using it, then you are the user

Ok do

Eoin

So, you have a table that stores the ID of the rep and the ID the county on each row. Everything I said earlier still applies in this scenario.

Protype

eoin wrote: »

So, you have a table that stores the ID of the rep and the ID the county on each row. Everything I said earlier still applies in this scenario.

ye i have a master table to store the ids

Eoin

OK, so:

One table to store the Reps
One table to store the Counties
One table to link the Reps AND Counties.

So, if you have a user with an ID of 3 who is assigned to Counties 4,5 and 6 then in your link table you would have something like this:

[B]RepID CountyID[/B]
   3      4
   3      5
   3      6

The link table contains three rows - one for each county that the rep is assigned to.

Protype

eoin wrote: »

OK, so:

One table to store the Reps
One table to store the Counties
One table to link the Reps AND Counties.

So, if you have a user with an ID of 3 who is assigned to Counties 4,5 and 6 then in your link table you would have something like this:

[B]RepID CountyID[/B]
   3      4
   3      5
   3      6

The link table contains three rows - one for each county that the rep is assigned to.

Change reps to customers

How will it work if i need to add new customers every day

Eoin

Each time you add a customer, you select the applicable counties.

Protype

eoin wrote: »

Each time you add a customer, you select the applicable counties.

In my database i have master table which stores the id of all the parent tables BUT i also store the details like first_name, surname, address and so. would you recommend i change this

Eoin

I don't really know what you mean by master/parent tables in this context.

Your customer table has an ID field. Your County table as an ID field. You now need a third completely separate table that stores the ID of the customer and the ID of the County in one row.

Therefore, if a customer is assigned to three counties, then there will be three rows in that table for the customer.

Protype

You see this is why im confused

Master table

Counties

Jobs

Master is where i join counties and jobs, do ye understand how

Protype

eoin wrote: »

Therefore, if a customer is assigned to three counties, then there will be three rows in that table for the customer.

when i have this 3rd table, is this something which happens automatically

Adam

Protype wrote: »

You see this is why im confused

Master table

Counties

Jobs

Master is where i join counties and jobs, do ye understand how

that's what eoin has been explaining.

when you submit your form for the customers details, the customers id and the countys id get stored in a database by themselves. there will be a new row for each county that the customer works in, each time you submit the form.

Protype

in the master table i have the name, surname, address, town, and county_id there is more but im not going to list them all

i also have a table for county which only has the id and county name

again i have more tables like this (county) table. i won't list them all

when i'm inserting new people into the database all goes to the master table
and now i don't know if i made a mess of my database doing it this way.

Protype

Sorry for the frustration

Thanks for the help