Board index   FAQ   Search  
Register  Login
Board index PHP PHP Scripts

Submit dropdown list values to mysql

Links for php scripts

Moderators: macek, egami, gesf

Submit dropdown list values to mysql

Postby kevz » Fri Mar 15, 2013 7:00 am

hi,

I have got 2 php scripts with javascript as shown below.

I got the dynamic drop down list to work but i do not know how to submit those values into another table in database. Basically the scenario is, I want the user to select values from the drop down list and when he clicks submit button, the values will be stored in another table (called booking) in the mysql database.
Any ideas?

The 2 current tables i got that goes with the scripts below are:
Countries and Cities

These are the scripts i got

index.php page
Code: Select all
<?php

$conn = mysql_connect("localhost", "root", "");
$db = mysql_select_db("test", $conn);

$sql_country = "SELECT * FROM country";
$result_country = mysql_query($sql_country);

?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Country List</title>
</head>
<body>
<?php

echo "<select name='country' onChange='get_cities(this.value)'>";

while($row_country = mysql_fetch_array($result_country))
{
    echo "<option value='".$row_country['id']."'>".$row_country['country']."</option>";
}
echo "</select>";
echo "<div id='cityLayer'><select name='city' id='city'></select></div>";
?>
    <script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script type="text/javascript">
    function get_cities($country_id){
     $.ajax({
         url : "city.php?country_id="+$country_id,
         cache : false,
         beforeSend : function (){
              //Show a message
         },
         complete : function($response, $status){
             if ($status != "error" && $status != "timeout") {
                 $('#cityLayer').html($response.responseText);
             }
         },
         error : function ($responseObj){
             alert("Something went wrong while processing your request.\n\nError => "
                 + $responseObj.responseText);
         }
     });
    }
 </script>
</body>
</html>


city.php page

Code: Select all
<?php

$conn = mysql_connect("localhost", "root", "");
$db = mysql_select_db("test", $conn);

$country_id = $_REQUEST['country_id'];
$sql_city = "SELECT * FROM cities WHERE country_id = '".$country_id."'";
$result_city = mysql_query($sql_city);

echo "<select name='city'>";
while($row_city = mysql_fetch_array($result_city))
{
   echo "<option value='".$row_city['id']."'>".$row_city['city']."</option>";
}
echo "</select>";
?>
kevz
New php-forum User
New php-forum User
 
Posts: 29
Joined: Fri Mar 15, 2013 6:50 am

Re: Submit dropdown list values to mysql

Postby seandisanti » Fri Mar 15, 2013 8:35 am

Ok, first you want to stop using the mysql_ functions, they're slower, and less secure than any of the alternatives. PDO has become the standard I believe, and it's very simple to implement either procedurally or using objects.

If you want a quick crash course on how easy PDO is, there's a short video at http://jream.com/learning/videos/php-oo ... o-examples that will be more than enough to get you started.

Now back to the advice you actually asked for. It will be hard to give you a working fix without knowing anything about your schema (aka table structure) but here is a short PDO Object based example of grabbing values from one table and inserting into another. I've bypassed the form completely, and just pulled all active foo fields from table bar, and then used a prepared statement to add each into new_table with a timestamp.
Code: Select all

<?php

    $db 
= new PDO('mysql:host=localhost;dbname=test'$user$pass);
    
$sql "SELECT foo FROM bar where active=1";
    
$result $db->query($sql);
    if ((!
$result)||$result.rowCount()==0){//if there's an error or no responses
        
die('something went wrong, try this query in phpmyadmin<br />'$sql);
    }
    
$updateString "INSERT INTO new_table(foo,add_date) values(:ack,NOW())";
    
$sth $db->prepare($updateString);
    while (
$row $result->fetch(PDO::FETCH_ASSOC)){//loop while there are valid rows in the result set
        
$sth->execute(array(':ack'=>$row['foo']));
    }
    
?>
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Submit dropdown list values to mysql

Postby kevz » Fri Mar 15, 2013 9:13 am

Thnx for the tip,

I will visit the tutorial and see if I can do it.
kevz
New php-forum User
New php-forum User
 
Posts: 29
Joined: Fri Mar 15, 2013 6:50 am

Re: Submit dropdown list values to mysql

Postby seandisanti » Fri Mar 15, 2013 9:20 am

You can. It is no tougher than the way you're currently trying to access the database, except it's more secure, and much faster. And you have all of us here to help with any issues you run into. You're always more likely to get help when you're trying to do things the right way. How likely would you be to help someone trying to put square tires onto their car?
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Submit dropdown list values to mysql

Postby kevz » Fri Mar 15, 2013 9:27 am

OK ....I have just seen the tutorial, it only shows how to put values into the database from the script end.

What i am trying to do is grab data from 1 table in the database and put them in dynamic drop down lists on the web form, then when the user picks those drop down lists on the web form and click submit button, those values then gets inserted into a different table in the database
kevz
New php-forum User
New php-forum User
 
Posts: 29
Joined: Fri Mar 15, 2013 6:50 am

Re: Submit dropdown list values to mysql

Postby kevz » Fri Mar 15, 2013 9:33 am

lol,

Im in the military, we can put square tires on any vehicle as long as we escape an ambush! :-)

Sorry but i am told to do a project on coach tours in europe using wamp and phpmyadmin. I only learnt web design in a week taught by military instructors. I am completely new to all this and I am in desperate help because i spent almost 3 weeks teaching my self how to do coding and running out of time. Project due soon.
kevz
New php-forum User
New php-forum User
 
Posts: 29
Joined: Fri Mar 15, 2013 6:50 am

Re: Submit dropdown list values to mysql

Postby seandisanti » Fri Mar 15, 2013 9:34 am

Ok, working with forms and form controls are easy also. Check out this video for a 7 minute crash course on passing data from one page to another http://jream.com/learning/videos/php-ba ... et-request and if you have any questions about using specific controls in your forms, they are explained in depth at http://www.w3schools.com/html/html_forms.asp
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Submit dropdown list values to mysql

Postby seandisanti » Fri Mar 15, 2013 9:36 am

No worries man, we'll help ya get it up and running, and if i sound critical it is meant to be constructive and not picking on you so much as the method
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Submit dropdown list values to mysql

Postby kevz » Fri Mar 15, 2013 9:55 am

I got my customer registration form working where a customer registers with a user name and password, then logs into the site where he/she can book a tour. Now the problem i got is the booking form. I can submit values into my booking table by using <option></option> method but this only allows me to put the values in hardcoding (inside the booking.php).

My project requires that the tour agency is able to update the webpage with forthcoming tours, which i managed to do. This is inserted into the tour table in the database.

The booking form should be able to pull the forthcoming tours from the tour table and list it in the booking form for the customer to select.

When a customer clicks the submit button in the booking form, those selected tours is then sent to another table in the database for the tour agency to view to see what tours have been booked for.

Sorry that i didn't explain it better earlier in the post. To be honest i still find it hard to understand alot of these scripts based on tutorials reference this problem i have. Is there a easier and better way to do this?
kevz
New php-forum User
New php-forum User
 
Posts: 29
Joined: Fri Mar 15, 2013 6:50 am

Re: Submit dropdown list values to mysql

Postby seandisanti » Fri Mar 15, 2013 10:17 am

There's always an easier and/or better way to do things. The beauty of programming in any language is that for any problem you encounter there are a number of ways to solve it. With practice your solutions become better as you understand the finer points more and more. Show me your code for your form, and I'll show you how to update it.

***edit***
Sorry I see now that you did put up your form. give me a minute and I'll have some code for you.
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Submit dropdown list values to mysql

Postby kevz » Fri Mar 15, 2013 10:25 am

The scripts i posted earlier is just a working example that i thought i could use to start off my booking form. It only allows 2 drop down lists as well.

edited:
If you need, i can post my original project booking form scripts
kevz
New php-forum User
New php-forum User
 
Posts: 29
Joined: Fri Mar 15, 2013 6:50 am

Re: Submit dropdown list values to mysql

Postby seandisanti » Fri Mar 15, 2013 10:36 am

it's alright; do you have to use Ajax though? personally I try to avoid client side scripting just because of security and the possibility that your visitor may have client side scripting disabled which would break the functionality.
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Submit dropdown list values to mysql

Postby kevz » Fri Mar 15, 2013 10:47 am

i dont want anything thats too complicated for me to understand like ajax. So yeah, we can leave it out if its easier.

edit:
If you got anything that would meet my project requirements will be fantastic!
kevz
New php-forum User
New php-forum User
 
Posts: 29
Joined: Fri Mar 15, 2013 6:50 am

Re: Submit dropdown list values to mysql

Postby seandisanti » Fri Mar 15, 2013 11:04 am

Here's a little code, i couldn't test because i haven't setup a database with those tables, but it should work for you with very little modification. It is a page that displays a form, and processes the form itself. on submit, it populates the city box based on the cities in posted country. countries are populated every time, and the selected country should remain after post. in theory, selecting a country should submit the form (that part may require another look because of the way it's quoted) and then the form when re-drawn will include cities for the selected country. give it a try and let me know if you have questions or errors you can't resolve.
Code: Select all
<?php

$db 
= new PDO("mysql:host=localhost;dbname=test""root""");
if (
$_POST) {//are we responding to a form? 
    
$country $_POST['country'];
    
$sql_city "SELECT * FROM city WHERE country_id =" $country;
    
$cityResult $db->query($sql_city);
    if ((!
$cityResult) || $cityResult rowCount == 0) {
        die(
'could not pull city names for country ' $country);
    }
}
$sql_country "SELECT * FROM country";
$countryResult $db->query($sql_country);
if ((!
$countryResult) || $countryResult rowCount() == 0) {
    die(
'no countries pulled from database');
}
echo 
'
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
    <head>
        <title>Country List</title>
    </head>
    <body>'
;
echo 
'<form id="frm" action="' $_SERVER['PHP_SELF'] . '" method="post">';
echo 
'<select name="country" onChange="document.getElementById("frm").submit()">';
while (
$row $countryResult->fetch(PDO::FETCH_ASSOC)) {
    echo 
'<option value="' $row['id'] . '"' . (($country == $row['id']) ? ' selected ' '') . '>' $row['country'] . '</option>';
}
echo 
"</select>";
echo 
'<div id="cityLayer"><select name="city" id="city">';
if (!empty(
$cityResult)) {
    while (
$cRow $cityResult->fetch(PDO::FETCH_ASSOC)) {
        echo 
'<option value="' $cRow['id'] . '">' $cRow['city'] . '</option>';
    }
}
echo 
'</select></div>
    </body>
    </html>'
;
 
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Submit dropdown list values to mysql

Postby kevz » Fri Mar 15, 2013 11:09 am

hey thanx alot man,

I will give it a go
kevz
New php-forum User
New php-forum User
 
Posts: 29
Joined: Fri Mar 15, 2013 6:50 am

Re: Submit dropdown list values to mysql

Postby seandisanti » Fri Mar 15, 2013 11:15 am

no problem, glad to help
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Submit dropdown list values to mysql

Postby kevz » Fri Mar 15, 2013 11:17 am

! ) SCREAM: Error suppression ignored for
( ! ) Fatal error: Call to undefined function rowCount() in C:\wamp\www\test\index.php on line 14

This is the error thats showing!
kevz
New php-forum User
New php-forum User
 
Posts: 29
Joined: Fri Mar 15, 2013 6:50 am

Re: Submit dropdown list values to mysql

Postby seandisanti » Fri Mar 15, 2013 11:20 am

my fault, i was thinking dot notation for a second I do that sometimes. try this; I've just replaced the ' . rowCount's with '->rowCount()'
Code: Select all

    
<?php

    $db 
= new PDO("mysql:host=localhost;dbname=test""root""");
    if (
$_POST) {//are we responding to a form? 
        
$country $_POST['country'];
        
$sql_city "SELECT * FROM city WHERE country_id =" $country;
        
$cityResult $db->query($sql_city);
        if ((!
$cityResult) || $cityResult->rowCount == 0) {
            die(
'could not pull city names for country ' $country);
        }
    }
    
$sql_country "SELECT * FROM country";
    
$countryResult $db->query($sql_country);
    if ((!
$countryResult) || $countryResult->rowCount() == 0) {
        die(
'no countries pulled from database');
    }
    echo 
'
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
        <head>
            <title>Country List</title>
        </head>
        <body>'
;
    echo 
'<form id="frm" action="' $_SERVER['PHP_SELF'] . '" method="post">';
    echo 
'<select name="country" onChange="document.getElementById("frm").submit()">';
    while (
$row $countryResult->fetch(PDO::FETCH_ASSOC)) {
        echo 
'<option value="' $row['id'] . '"' . (($country == $row['id']) ? ' selected ' '') . '>' $row['country'] . '</option>';
    }
    echo 
"</select>";
    echo 
'<div id="cityLayer"><select name="city" id="city">';
    if (!empty(
$cityResult)) {
        while (
$cRow $cityResult->fetch(PDO::FETCH_ASSOC)) {
            echo 
'<option value="' $cRow['id'] . '">' $cRow['city'] . '</option>';
        }
    }
    echo 
'</select></div>
        </body>
        </html>'
;
 
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Submit dropdown list values to mysql

Postby kevz » Fri Mar 15, 2013 11:26 am

SCREAM: Error suppression ignored for

( ! ) Notice: Undefined variable: country in C:\wamp\www\test\index.php on line 28

It is coming with this error now.

edit:
This is my line 28.

echo '<option value="' . $row['id'] . '"' . (($country == $row['id']) ? ' selected ' : '') . '>' . $row['country'] . '</option>';
kevz
New php-forum User
New php-forum User
 
Posts: 29
Joined: Fri Mar 15, 2013 6:50 am

Re: Submit dropdown list values to mysql

Postby seandisanti » Fri Mar 15, 2013 11:28 am

change line 27 to read
Code: Select all
echo '<option value="' . $row['id'] . '"' . ((isset($country) && $country == $row['id']) ? ' selected ' : '') . '>' . $row['country'] . '</option>';

$country is only set when responding to a post, so it would error as not set when not responding to post. adding the isset() to the condition fixes it
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Submit dropdown list values to mysql

Postby kevz » Fri Mar 15, 2013 11:32 am

sorry to be a pain,

It did fix it (no errors are showing) but now the 2nd drop down list is not showing any values on the web browser.
kevz
New php-forum User
New php-forum User
 
Posts: 29
Joined: Fri Mar 15, 2013 6:50 am

Re: Submit dropdown list values to mysql

Postby seandisanti » Fri Mar 15, 2013 11:39 am

it's probably not posting the form. one sec.
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Submit dropdown list values to mysql

Postby kevz » Fri Mar 15, 2013 11:44 am

Question?

does the form need a submit button for the user to click when he/she finish selecting the values.
And the values selected has to go in a new table, right?
kevz
New php-forum User
New php-forum User
 
Posts: 29
Joined: Fri Mar 15, 2013 6:50 am

Re: Submit dropdown list values to mysql

Postby seandisanti » Fri Mar 15, 2013 11:45 am

alright; made some changes. now the action on the form is dynamic; if $_POST['city'] is set, then the form processes to wherever you want to process the full form. Otherwise it posts back to itself to populate the city list. Along the same lines, the text on the submit button shows 'get cities' or 'submit form' based on the same context.
Code: Select all

    
<?php

    $db 
= new PDO("mysql:host=localhost;dbname=test", "root", "");
    if ($_POST) {//are we responding to a form? 
        $country = $_POST['country'];
        $sql_city = "SELECT * FROM city WHERE country_id =" . $country;
        $cityResult = $db->query($sql_city);
        if ((!$cityResult) || $cityResult->rowCount == 0) {
            die('could not pull city names for country ' . $country);
        }
    }
    $sql_country = "SELECT * FROM country";
    $countryResult = $db->query($sql_country);
    if ((!$countryResult) || $countryResult->rowCount() == 0) {
        die('no countries pulled from database');
    }
    echo '
    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
        <head>
            <title>Country List</title>
        </head>
        <body>'
;
    echo '<form id="frm" action="' . ((isset($_POST['city'])?'fullform.php':$_SERVER['PHP_SELF'])) . '" method="post">';
    echo '<select name="country">';
    while ($row = $countryResult->fetch(PDO::FETCH_ASSOC)) {
        echo '<option value="' . $row['id'] . '"' . ((isset($country) && $country == $row['id']) ? ' selected ' : '') . '>' . $row['country'] . '</option>';
    }
    echo "</select>";
    echo '<div id="cityLayer"><select name="city" id="city">';
    if (!empty($cityResult)) {
        while ($cRow = $cityResult->fetch(PDO::FETCH_ASSOC)) {
            echo '<option value="' . $cRow['id'] . '">' . $cRow['city'] . '</option>';
        }
    }
    echo '</select></div>
        <input type="submit" value="'
 . ((isset($_POST['country']))?'Get cities':'Submit form') . '" />';
    echo '</body>
        </html>'
;
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Re: Submit dropdown list values to mysql

Postby seandisanti » Fri Mar 15, 2013 11:46 am

kevz wrote:Question?

does the form need a submit button for the user to click when he/she finish selecting the values.
And the values selected has to go in a new table, right?

I was trying to have it submit the form automatically on change to country, which i suspected may not work because of the way that it was quoted. rather than mess with that to make it work with JS, i just added a dynamic submit in the example above.
seandisanti
php-forum Fan User
php-forum Fan User
 
Posts: 838
Joined: Mon Oct 01, 2012 12:32 pm

Next

Return to PHP Scripts

Who is online

Users browsing this forum: Yahoo [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.