22 Replies Latest reply on Jul 31, 2015 5:52 AM by osgood_

    Link form with mysql table

    trenholme900 Level 1

      How would I go about linking my form with a mysql table and from there can I link the information pulled to set a variable in php? I want a warning to appear in the form when the user tries to book too many a people on a specific date. Can I get the number of spaces available from the database and put it in the $spaces variable?


      I  started writing this code but didn't know where to go from there:


      <?php

       

        $spaces = 20;

        $num_people = $_GET['people'];

        $message = "Unfortunately we don't have this many spaces available on this date. We have a maximum of $spaces.";

            

              if($spaces < $num_people) {

                echo $message;

              }

      ?>


      <form method="get">

          Name:<br>

              <textarea id="name"></textarea><br>

              <br>

              Date leaving:<br>

              <br>

              <textarea id="date"></textarea>

                          <div class="contact_response">

                                    <?php echo $message; ?>

                          </div>

              <br>

              <br>

              How many people?:<br>

              <textarea id="people" name="people"></textarea><br>

              <br>

              <input type="submit">

            

      </form>

        • 1. Re: Link form with mysql table
          Ben M Adobe Community Professional

          It's definitely possible to do this.  The query itself would depend on the structure of your database.

           

          The code currently at the top has some reasoning behind it, but won't accomplish what you need.  The global $_GET variable is for items in the query string of a browser.  It's usually not secure to be trying to get the number via this method.  Typically in PHP you would just dump the query into a string and your resulting code would be more similar to:

           

          if ( $event['spaces'] >= $event['registered'] )

          {

          print "";  // Print your error message HTML here and do not load the form

          }

          else { print ""; } //Print your form version of the page here with no error displayed.

           

          What you would be doing in this example, is saving the data from the query in an array (event) and then calling out the columns in the array (spaces, registered), and printing different versions of the page depending on whether there are spaces available.

           

          The main issue with your version as is stands is that it loads the form regardless, so why make someone register if there are no spaces available.  I would leave a version of the code though after submission to check this as well in case multiple visitors load the page at the same time and try to all submit your form at once.

           

          If you need further help, you may need to provide details about your database structure to look further into things like your specific queries.

          1 person found this helpful
          • 2. Re: Link form with mysql table
            trenholme900 Level 1

            Thanks Ben. The form isn't a registration form its just to find out what

            their needs are for the holiday.

             

            The problem is the spaces available per date will change so they need to be

            manually changed in the database and then pulled automatically by the code.

             

            How do I pull the number of available spaces from the database and put it

            into the $spaces variable? It's a standard MySQL table created in phpmyadmin

            • 3. Re: Link form with mysql table
              Ben M Adobe Community Professional

              There's not really much that's standard about a MySQL table other than it's a table.  It would be like saying all Excel spreadsheets are identical.

               

              The query statement would look like like:

               

              SELECT * FROM {TABLE} WHERE {COLUMN_NAME} = ".$_GET['Event_Name']."

               

              That is an example query that would need to be run in your PHP script because it includes variables in the query.  Personally, I wouldn't SELECT all of the data from the table as this query does.  It's just a bad habit to break down the road if you make more complex systems because more data queried = more time spent on query and that leads to longer load times and increased memory loads on a server. 

              1 person found this helpful
              • 4. Re: Link form with mysql table
                osgood_ Level 8

                trenholme900 wrote:

                 

                 

                How do I pull the number of available spaces from the database and put it

                into the $spaces variable? It's a standard MySQL table created in phpmyadmin

                 

                Are you wanting to write the mysql or mysqli and php yourself or are you using the Dreamweaver server behaviours?

                • 6. Re: Link form with mysql table
                  trenholme900 Level 1

                  Wanting to write it myself I think. Also can I input data into the database

                  manually or does it have to come through the form?

                  • 7. Re: Link form with mysql table
                    osgood_ Level 8

                    trenholme900 wrote:

                     

                    Wanting to write it myself I think. Also can I input data into the database

                    manually or does it have to come through the form?

                     

                    You can input data directly into your database manually. I do this all the time where I update websites and cant be bothered to write an admin section as I'm the only administrator for the site.

                     

                    Let me see if I can write a bit of code so you can work with it.

                    • 8. Re: Link form with mysql table
                      trenholme900 Level 1

                      Thanks osgood

                      • 9. Re: Link form with mysql table
                        osgood_ Level 8

                        trenholme900 wrote:

                         

                        Thanks osgood

                         

                        Humm this may be a bit more tricky than what you imagine - I do this all the time.

                         

                        So you have a number of holidays listed in your database. How does the form relate to the holiday? Do users click through to a holidays details page where the information about the holiday is listed dynamically from the database and this is where the form is included? I say this because to get the correct available spaces for each holiday you have tell the database which holiday to look for in the database when the form submit button is clicked. I would normally do this via a hidden form field which houses the holiday_id of the holiday which is the unique primary column in the database

                        • 10. Re: Link form with mysql table
                          trenholme900 Level 1

                          So basically this the person has come to a page where they decide what options they want for their holiday package. There are only 2 options, they will choose one and then pick the dates and various other options which are all standard. So yes depending on the destination the availability will change for dates. I was thinking that when there are fewer places for a date, I would go directly into the table and change the number of available places for that date. The hidden field sounds like a good idea

                          • 11. Re: Link form with mysql table
                            osgood_ Level 8

                            trenholme900 wrote:

                             

                            So basically this the person has come to a page where they decide what options they want for their holiday package. There are only 2 options, they will choose one and then pick the dates and various other options which are all standard. So yes depending on the destination the availability will change for dates. I was thinking that when there are fewer places for a date, I would go directly into the table and change the number of available places for that date. The hidden field sounds like a good idea

                             

                            Ok so somehow the user has to get to the holidays package page where the details are listed. Lets assume they have come from a link where the holidays have been listed?

                             

                            Could this work for you? For testing purposes create a database table named 'holidays'. Create 3 columns named -  holiday_id   -  holiday_name   -  holiday_spaces

                             

                            Make the holiday_id column the primary key and auto increment (this will be the unique record identifier).

                             

                            Populate the columns with a few bits of test data.

                             

                            Copy and paste the below code into a DW document (change the connection details 'localhost' , 'username , 'password , 'database_name' to those of your own details).

                             

                            Be aware of this line in the code $holiday_id = 2; at the moment it is hard coded (you can change it to an existing holiday_id in the database for testing purposes. In reality the number will come from a link like <a href="holiday_details.php?holiday_id=<?php echo $row['holiday_id']; ?">Carribean Cruise</a>. You can dynamically generate the links from the database or hand code the links like <a href="holiday_details.php?holiday_id=2">Carribean Cruise</a> BUT the number MUST match the holiday_id in the database.

                             

                            You would then replace $holiday_id = 2; with $holiday_id = $_GET['holiday_id'];

                             

                            I generally just create the links by looping through the database.

                             

                             

                            <?php

                            $conn = new mysqli('localhost' , 'username , 'password , 'database_name');

                            if($conn->connect_errno) {

                            echo $conn->connect_error;

                            die('Sorry, cannot connect at moment, try later');

                            }

                            ?>

                            <?php

                            $holiday_id = 2;

                            $getHolidayDetails = $conn->query("SELECT * FROM holidays WHERE holiday_id='$holiday_id'") or die($conn->error);

                            // append information to an array

                            $getHolidayDetails = $getHolidayDetails->fetch_assoc();

                            ?>

                             

                             

                            <?php

                            if(isset($_POST['submit'])) {

                            $people = $_POST['people'];

                            //get holiday_id from the hidden form field

                            $holiday_id = $_POST['holiday_id'];

                            // get holiday spaces based on holiday_id column in database

                            $getHolidaySpaces = $conn->query("SELECT holiday_spaces FROM holidays WHERE holiday_id='$holiday_id'") or die($conn->error);

                            // append information to an array

                            $getHolidaySpaces = $getHolidaySpaces->fetch_assoc();

                            // append holiday spaces to a variable

                            $holiday_spaces = $getHolidaySpaces['holiday_spaces'];

                            }

                            ?> 

                            <!DOCTYPE html>

                            <html>

                            <head>

                            <meta charset="UTF-8" />

                            <title>Untitled Document</title>

                            </head>

                             

                            <body>

                            <h2><?php echo $getHolidayDetails['holiday_name']; ?></h2>

                            <form name="holiday_details" method="post" action="">

                            Name:<br>

                            <textarea id="name"></textarea><br>

                            <br>

                            Date leaving:<br>

                            <br>

                            <textarea id="date"></textarea>

                            <div class="contact_response"></div>

                            <br>

                            <br>

                            How many people?:<br>

                            <textarea id="people" name="people"></textarea><br>

                            <?php if($holiday_spaces < $people) {

                                echo "<h3>Sorry there are only ".$holiday_spaces." spaces available</h3>";

                            }

                                ?>

                            <br>

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

                            <input type="submit" name="submit" value="submit">

                            </form>

                            </body>

                            </html>

                            • 12. Re: Link form with mysql table
                              trenholme900 Level 1

                              That works great Osgood but the only thing is it doesn't change for a specific date. For example there might be only 8 spaces available in holiday_1 on the 24/07/16 but 25 on the 30/08/16. What if we just added a 'date' row into the table and that could be changed manually along with the spaces as they company finds them out?

                              • 13. Re: Link form with mysql table
                                osgood_ Level 8

                                trenholme900 wrote:

                                 

                                That works great Osgood but the only thing is it doesn't change for a specific date. For example there might be only 8 spaces available in holiday_1 on the 24/07/16 but 25 on the 30/08/16. What if we just added a 'date' row into the table and that could be changed manually along with the spaces as they company finds them out?

                                 

                                This is where its going to get tricky because if you dont store each holiday date in an individual entry in the database you then need to store the dates and the spaces in a seperate table and use a foreign-key (holiday_id) to associate them with the holiday then check the spaces available against the date chosen by the user.

                                 

                                I think allowing the user to enter a date is hit or miss - you really have to do that for them otherwise they wont enter the date in the correct format which is needed to check against the format in the database. So it would be best to echo the available dates to a <select> list with the name 'holiday_date' and let the user choose from the list of available dates.

                                 

                                $holiday_date = $_POST['holiday_date'];

                                 

                                I can't see another simple way of doing this apart from seperating the dates and available spaces into their own table.

                                 

                                You can then query the database for SELECT holiday_spaces FROM holiday_dates WHERE holiday_id = holiday_id AND holiday_date = holiday_date

                                • 14. Re: Link form with mysql table
                                  trenholme900 Level 1

                                  Excellent. I've made the 'select' tag with dates. I added this to the php:

                                   

                                  $holiday_date = $_POST['holiday_date'];

                                  // get holiday spaces based on holiday_id column in database

                                  $getHolidaySpaces = $conn->query("SELECT holiday_spaces and holiday_date FROM holidays WHERE holiday_id='$holiday_id'") or die($conn->error);

                                   

                                  When the error message came up it didn't state how many spaces there were available. It just said "Sorry there are only spaces available" It also says this no matter what number I type in which is obviously not right. Could you give me a hand with this last little bit?

                                  • 15. Re: Link form with mysql table
                                    osgood_ Level 8

                                    Comma seperate the columns you want to select from the database table - see below - does that make a difference?

                                     

                                     

                                    $getHolidaySpaces = $conn->query("SELECT holiday_spaces, holiday_date FROM holidays WHERE holiday_id='$holiday_id'") or die($conn->error);

                                    • 16. Re: Link form with mysql table
                                      trenholme900 Level 1

                                      It does it comes back with a number now. However the number is always 8. It's beacuse there is the code

                                       

                                      $holiday_id = 2;

                                       

                                      The number of places in holiday_id= 2 is 8. I tried replacing the 2 with $_POST['holiday_id'] but that didn't work

                                      • 17. Re: Link form with mysql table
                                        osgood_ Level 8

                                        trenholme900 wrote:

                                         

                                        It does it comes back with a number now. However the number is always 8. It's beacuse there is the code

                                         

                                        $holiday_id = 2;

                                         

                                        The number of places in holiday_id= 2 is 8. I tried replacing the 2 with $_POST['holiday_id'] but that didn't work

                                         

                                        You have to pass the holiday_id via a link.

                                         

                                        Say we had this:

                                         

                                        Carribean Cruise Dates

                                         

                                        1/8/2015 - View Details

                                        8/8/2015 - View Details

                                        21/8/2015 - View Details

                                         

                                        View Details would be a link like:

                                         

                                        1/8/2015 <a href="holiday_details.php?holiday_id=5">View Details</a>

                                        8/8/2015 <a href="holiday_details.php?holiday_id=16">View Details</a>

                                        21/8/2015 <a href="holiday_details.php?holiday_id=21">View Details</a>

                                         

                                        The link is passed to the holidays_details.php page where the form is located and the user can then select the date they require from the select field and enter the number of people travelling. That information is then checked against what is in the database.

                                         

                                        $holiday_id = $_GET['holiday_id'];

                                         

                                        This actually negates any reason to offer a dates option in the form as the user SELECTS the dates via the link - ALL you are doing then is checking the spaces available for that date where the holiday_id in the database is a match against the holiday_id which is passed via the link.

                                         

                                        So you can have 3 entries in your database for Carribean Cruises each entry sharing the main itinerary information but different spaces available and different dates.

                                         

                                        Of course this is not the optimum way of doing because you duplicate some information but it will be the simplest for you as I think storing the dates and spaces in a seperate table is way beyond your skills level at this particular moment.

                                        • 18. Re: Link form with mysql table
                                          trenholme900 Level 1

                                          So do I need to include the holiday_id with each date?

                                           

                                          <option id="holiday_id=1" >23/07/2016</option>

                                          • 19. Re: Link form with mysql table
                                            osgood_ Level 8

                                            trenholme900 wrote:

                                             

                                            So do I need to include the holiday_id with each date?

                                             

                                            <option id="holiday_id=1" >23/07/2016</option>

                                             

                                             

                                            What you have there is meaningless its just an id and I have no idea why you have included it in the option tag. If youre planning to go down the route you are, filtering the information by date and holiday_id then you need to include the date in the option tag as it appears in the database: I'm guessing here you havent actually used a 'date' assigned column else it would need to be in this format value="2016-07-23"

                                             

                                            <option value="23/07/2016" >23/07/2016</option>

                                             

                                            I think you need to sit down and work through exactly how this works because just hoping things will work, won't.

                                             

                                            I'm not sure why you dont want to have a landing page where the holidays/dates are listed which then link through to a details page, simple.

                                            • 20. Re: Link form with mysql table
                                              trenholme900 Level 1

                                              There will be a landing page

                                              • 21. Re: Link form with mysql table
                                                trenholme900 Level 1

                                                The landing page will have the details on of the package then try go to

                                                this form to chose what package they want and when etc

                                                • 22. Re: Link form with mysql table
                                                  osgood_ Level 8

                                                  trenholme900 wrote:

                                                   

                                                  The landing page will have the details on of the package then try go to

                                                  this form to chose what package they want and when etc

                                                   

                                                  Then on the landing page which you say will list the package details why arent you including the dates on that page? It seems pointless to me to have a details page with no dates listed on it?

                                                   

                                                  All you need do then is query the database for the available spaces. Infact I'm not even sure why you want a user to have to go through even that process when you can list the available spaces on the details page which avoids them having to make a stab in the dark and then be told there is only this amount of spaces available.

                                                   

                                                  The only time you need to really check anything against the database is when the user is making a purchase to make sure they are not trying to buy something thats is not available......until that point they should be able to see what is available on the details page for a particular destination without having to supply any information to you. Its just a waste of their time trying to second guess how many spaces are available.