Inserting infomation into mysql

Protype · 02-02-2009 4:21pm #1

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 · 02-02-2009 4:29pm

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

Protype · 02-02-2009 4:36pm

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 · 02-02-2009 4:42pm

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 · 02-02-2009 4:52pm

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 · 02-02-2009 4:58pm

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 · 02-02-2009 5:03pm

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 · 02-02-2009 5:06pm

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 · 02-02-2009 5:07pm

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 · 02-02-2009 5:08pm

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 · 02-02-2009 5:09pm

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 · 02-02-2009 5:11pm

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 · 02-02-2009 5:15pm

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 · 02-02-2009 5:18pm

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 · 02-02-2009 5:20pm

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 · 02-02-2009 5:22pm

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 · 02-02-2009 5:22pm

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 · 02-02-2009 5:24pm

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 · 02-02-2009 5:28pm

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 · 02-02-2009 5:29pm

Mirror wrote: »

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

Sorry statement

Adam · 02-02-2009 5:30pm

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 · 02-02-2009 5:33pm

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 · 02-02-2009 5:34pm

Mirror wrote: »

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

Ok do

Eoin · 02-02-2009 5:35pm

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 · 02-02-2009 5:37pm

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 · 02-02-2009 5:44pm

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 · 02-02-2009 5:54pm

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 · 02-02-2009 5:59pm

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

Protype · 02-02-2009 6:12pm

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 · 02-02-2009 6:53pm

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 · 02-02-2009 7:14pm

You see this is why im confused

Master table

Counties

Jobs

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

Inserting infomation into mysql

Comments