12 Replies Latest reply on Jan 4, 2012 3:49 AM by MurraySummers

    Any ideas on how to incorporate a simple database into my website?

    adamjewsbury20

      Firstly I am to create a website that will survey the user on what their ideal laptop would be (each answer would relate to a certain brand or model of laptop); once the user has filled out the survey I went to be able to tally up their results to work out which record in the pre-existing database would be best suited...

       

      This would be a simplified example,

       

      Which brand would you prefer:

      Apple  ( )        HP      ( )         Sony    ( )

       

      Price range:

      100   ( )       200    ( )     300     ( )

       

      I have attempted to just 'search the database' since each answer has is representing a record in the database; however this does not take into account various answers across the survey (Will only search and display for the price, while ignoring what brand, etc.)

       

      The database is on phpmyadmin, whether that makes any difference.

       

      Perhaps there is a simple line of code that will handle all this, but I'm relatively new to the whole thing so any help would be greatly appreciated.

        • 1. Re: Any ideas on how to incorporate a simple database into my website?
          adamjewsbury20 Level 1

          Or if anyone has any ideas on what other way I could incorporate a database; that I can add/delete/search records on it, that could be suitable for an ideal laptop website..

          I've considered a subscription newsletter??

          • 2. Re: Any ideas on how to incorporate a simple database into my website?
            MurraySummers Level 8

            I'm not quite clear on what you are looking for.  Is it that you want to provide a service whereby the visitor can fill out the survey form, and the website then analyzes that data and compares against a fixed database to recommend which laptop comes closest to the input data across the board?

             

            Some questions if so:

             

            1. What is your skill level with PHP? 
            2. Do you have a standard for a 'near match' (in other words, how close do I have to come to the fixed data in each field to be scored as a match)? 
            3. And how many results do you want to display?
            4. How many records are in the database?
            5. How many fields are in each record?
            6. Do you want to search across all fields?
            • 3. Re: Any ideas on how to incorporate a simple database into my website?
              adamjewsbury20 Level 1

              Yes that's exactly it, couldn't quite manage the words for it though.

               

              1.     Not great unfortunately, only been studying it a couple of months now.

              2.     Well the user will click on a radio button from one of the options, so each radio button is valued accoringly in relation to that field on the database. i.e the radio button for 'Apple' would match the record 'Apple' within the Brand field.

              3.     Well since I'm struggling I feel that achieving 1 result for now would suffice.

              4.     Well for the purpose of this I feel that I may stick to 7 records in the database; one for each brand that I'm including, while they will have different price ranges, ratings, etc.

              5.     There are 6 fields in the database: ID, brand, model, price, rating, and location.

              6.     The user will only be asked questions on brand, price, rating and location. Preferably they wouldn't be required to answer all the questions; they may only be wanting to see what they can get online or at PC World.

              • 4. Re: Any ideas on how to incorporate a simple database into my website?
                MurraySummers Level 8

                1.     Not great unfortunately, only been studying it a couple of months now.

                 

                Everybody gotta be somewhere. Stick with it!

                 

                2.     Well the user will click on a radio button from one of the options, so each radio button is valued accoringly in relation to that field on the database. i.e the radio button for 'Apple' would match the record 'Apple' within the Brand field.

                 

                The trick comes in when you have to select a price range.  But I think you can simplify that process by defining the ranges, e.g.,

                 

                RADIO BUTTON VALUE  ---- PRICE RANGE

                               1                              $100 - $200

                               2                              $201 - $400

                               3                              $401 - $600

                 

                etc.

                 

                Then you can compare the submitted radio button's value to the same value in the database field (you would either need to create a new field to hold this range value, or alternatively calculate it on the fly knowing that range schedule).  If you elect to calculate it on the fly, you'd need to do something like this -

                 

                switch ($_POST['price_range']) {

                     case 1:

                          $min = 100;

                          $max = 200;

                     break;

                     case 2:

                          $min = 201;

                          $max = 400;

                     break;

                     case 3:

                          $min = 401;

                          $max = 600;

                     break;

                }

                 

                That would give you a $min and a $max that you can then use in your SELECT statement, e.g.,

                 

                "SELECT * from `table` WHERE price >= $min AND price <= $max AND ..."

                 

                The rest of the fields seem like they would be straight comparisons, e.g.,

                 

                "... AND brand = $brand AND ..."

                 

                Make sense?

                • 5. Re: Any ideas on how to incorporate a simple database into my website?
                  adamjewsbury20 Level 1

                  <?php require_once('../Connections/survey.php'); ?>

                  <?php

                  if (!function_exists("GetSQLValueString")) {

                  function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

                  {

                    if (PHP_VERSION < 6) {

                      $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

                    }

                   

                    $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

                   

                    switch ($theType) {

                      case "text":

                        $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                        break;   

                      case "long":

                      case "int":

                        $theValue = ($theValue != "") ? intval($theValue) : "NULL";

                        break;

                      case "double":

                        $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

                        break;

                      case "date":

                        $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                        break;

                      case "defined":

                        $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

                        break;

                    }

                    return $theValue;

                  }

                  }

                  $colname_Recordset1 = "-1";

                  if (isset($_GET['Brand'])) {

                    $colname_Recordset1 = $_GET['Brand'];

                  }

                  mysql_select_db($database_survey, $survey);

                  $query_Recordset1 = sprintf("SELECT * FROM ideal WHERE Brand = %s", GetSQLValueString($colname_Recordset1, "text"));

                  $Recordset1 = mysql_query($query_Recordset1, $survey) or die(mysql_error());

                  $row_Recordset1 = mysql_fetch_assoc($Recordset1);

                  $totalRows_Recordset1 = mysql_num_rows($Recordset1);

                   

                  $colname_Recordset1 = "-1";

                  if (isset($_GET['Price'])) {

                    $colname_Recordset1 = $_GET['Price'];

                  }

                  mysql_select_db($database_survey, $survey);

                  $query_Recordset1 = sprintf("SELECT * FROM ideal WHERE Price = %s", GetSQLValueString($colname_Recordset1, "text"));

                  $Recordset1 = mysql_query($query_Recordset1, $survey) or die(mysql_error());

                  $row_Recordset1 = mysql_fetch_assoc($Recordset1);

                  $totalRows_Recordset1 = mysql_num_rows($Recordset1);

                   

                  $colname_Recordset1 = "-1";

                  if (isset($_GET['Rating'])) {

                    $colname_Recordset1 = $_GET['Rating'];

                  }

                  mysql_select_db($database_survey, $survey);

                  $query_Recordset1 = sprintf("SELECT * FROM ideal WHERE Rating = %s", GetSQLValueString($colname_Recordset1, "text"));

                  $Recordset1 = mysql_query($query_Recordset1, $survey) or die(mysql_error());

                  $row_Recordset1 = mysql_fetch_assoc($Recordset1);

                  $totalRows_Recordset1 = mysql_num_rows($Recordset1);

                   

                  ?>

                   

                  That is the code I have; sorry its pretty long. The italics are basically doing the same as the 'Brand' part, they sort of take priority over it I guess which means that they aren't all being assessed instead just the rating.

                   

                  I can manage to get each search working, I can search for a brand, price, rating individually, but when attempting it together is where I'm struggling, can't get it to search along with each other if you can make heads or tails of that.

                  • 6. Re: Any ideas on how to incorporate a simple database into my website?
                    MurraySummers Level 8

                    You are pulling 3 different recordsets on the page, but each has the same NAME!  Thus the only one that sticks is the last one, since each previous one gets overwritten.  You can go this route, but you'd need to change the code to pull 3 unique recordsets, e.g.,

                     

                    $colname_Brand = "-1";

                    if (isset($_GET['Brand'])) {

                      $colname_Brand = $_GET['Brand'];

                    }

                    mysql_select_db($database_survey, $survey);

                    $query_Brand = sprintf("SELECT * FROM ideal WHERE Brand = %s", GetSQLValueString($colname_Brand, "text"));

                    $Brand = mysql_query($query_Brand, $survey) or die(mysql_error());

                    $row_Brand = mysql_fetch_assoc($Brand);

                    $totalRows_Brand = mysql_num_rows($Brand);

                     

                    $colname_Price = "-1";

                    if (isset($_GET['Price'])) {

                      $colname_Price = $_GET['Price'];

                    }

                    mysql_select_db($database_survey, $survey);

                    $query_Price = sprintf("SELECT * FROM ideal WHERE Price = %s", GetSQLValueString($colname_Price, "text"));

                    $Price = mysql_query($query_Price, $survey) or die(mysql_error());

                    $row_Price = mysql_fetch_assoc($Price);

                    $totalRows_Price = mysql_num_rows($Price);

                     

                    $colname_Rating= "-1";

                    if (isset($_GET['Rating'])) {

                      $colname_Rating = $_GET['Rating'];

                    }

                    mysql_select_db($database_survey, $survey);

                    $query_Rating = sprintf("SELECT * FROM ideal WHERE Rating = %s", GetSQLValueString($colname_Rating, "text"));

                    $Rating = mysql_query($query_Rating, $survey) or die(mysql_error());

                    $row_Rating = mysql_fetch_assoc($Rating);

                    $totalRows_Rating = mysql_num_rows($Rating);

                     

                    Now you have all the records that match the brand in $Brand, all the records that match the price in $Price and all the records that match the rating in $Rating.  That's probably still not what you want, though.

                     

                    Alternatively, you could do them all by replacing everything you have with this -

                     

                    $colname_Brand = "%";

                    if (isset($_GET['Brand'])) {

                      $colname_Brand = $_GET['Brand'];

                    }

                    $colname_Price = "%";

                    if (isset($_GET['Price'])) {

                      $colname_Price = $_GET['Price'];

                    }

                    $colname_Rating = "%";

                    if (isset($_GET['Rating'])) {

                      $colname_Rating = $_GET['Rating'];

                    }

                    mysql_select_db($database_survey, $survey);

                    $query_Results = "SELECT * FROM ideal WHERE Brand LIKE $colname_Brand AND Price LIKE $colname_Price AND Rating LIKE $colname_Rating));

                    $Results = mysql_query($query_Results, $survey) or die(mysql_error());

                    $row_Results = mysql_fetch_assoc($Results);

                    $totalRows_Results = mysql_num_rows($Results);

                     

                    You now have all the records that match brand, price and ratings in a single recordset.

                     

                    Isn't that what you wanted?

                    • 7. Re: Any ideas on how to incorporate a simple database into my website?
                      adamjewsbury20 Level 1

                      Having a bit of trouble getting it to work, would you mind looking over my code to see where I've messed up?

                       

                      <?php require_once('../Connections/survey.php'); ?>

                      <?php

                      if (!function_exists("GetSQLValueString")) {

                      function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

                      {

                        if (PHP_VERSION < 6) {

                          $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

                        }

                       

                        $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

                       

                        switch ($theType) {

                          case "text":

                            $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                            break;   

                          case "long":

                          case "int":

                            $theValue = ($theValue != "") ? intval($theValue) : "NULL";

                            break;

                          case "double":

                            $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

                            break;

                          case "date":

                            $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                            break;

                          case "defined":

                            $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

                            break;

                        }

                        return $theValue;

                      }

                      }

                      $colname_Brand = "-1";

                       

                      if (isset($_GET['Brand'])) {

                       

                        $colname_Brand = $_GET['Brand'];

                       

                      }

                       

                      mysql_select_db($database_survey, $survey);

                       

                      $query_Brand = sprintf("SELECT * FROM ideal WHERE Brand = %s", GetSQLValueString($colname_Brand, "text"));

                       

                      $Brand = mysql_query($query_Brand, $survey) or die(mysql_error());

                       

                      $row_Brand = mysql_fetch_assoc($Brand);

                       

                      $totalRows_Brand = mysql_num_rows($Brand);

                       

                       

                       

                      $colname_Price = "-1";

                       

                      if (isset($_GET['Price'])) {

                       

                        $colname_Price = $_GET['Price'];

                       

                      }

                       

                      mysql_select_db($database_survey, $survey);

                       

                      $query_Price = sprintf("SELECT * FROM ideal WHERE Price = %s", GetSQLValueString($colname_Price, "text"));

                       

                      $Price = mysql_query($query_Price, $survey) or die(mysql_error());

                       

                      $row_Price = mysql_fetch_assoc($Price);

                       

                      $totalRows_Price = mysql_num_rows($Price);

                       

                       

                       

                      $colname_Rating= "-1";

                       

                      if (isset($_GET['Rating'])) {

                       

                        $colname_Rating = $_GET['Rating'];

                       

                      }

                       

                      mysql_select_db($database_survey, $survey);

                       

                      $query_Rating = sprintf("SELECT * FROM ideal WHERE Rating = %s", GetSQLValueString($colname_Rating, "text"));

                       

                      $Rating = mysql_query($query_Rating, $survey) or die(mysql_error());

                       

                      $row_Rating = mysql_fetch_assoc($Rating);

                       

                      $totalRows_Rating = mysql_num_rows($Rating);

                       

                       

                      ?>

                       

                      <!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>

                      <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

                      <title>Search Results</title>

                      </head>

                       

                      <body>

                       

                      <h1> Survey results</h1>

                      <?php

                          $temp = $totalRows_Results;

                          if ($temp == 0) { echo "No Records Found"; }

                      ?>

                      <table width="200" border="1">

                        <tr>

                          <td>id</td>

                          <td>Brand</td>

                          <td>Name</td>

                          <td>Price</td>

                          <td>Rating</td>

                          <td>Purpose</td>

                        </tr>

                        <?php do { ?>

                        <tr>

                          <td><?php echo $row_Results['id']; ?></td>

                          <td><?php echo $row_Results['Brand']; ?></td>

                          <td><?php echo $row_Results['Name']; ?></td>

                          <td><?php echo $row_Results['Price']; ?></td>

                          <td><?php echo $row_Results['Rating']; ?></td>

                          <td><?php echo $row_Results['Purpose']; ?></td>

                        </tr>

                        <?php } while ($row_Results = mysql_fetch_assoc($totalRowResults)); ?>

                      </table>

                       

                      <br />

                      <a href="survey.html">Back to survey</a>

                      </body>

                      </html>

                      <?php

                      mysql_free_result($Results);

                      ?>

                      • 8. Re: Any ideas on how to incorporate a simple database into my website?
                        MurraySummers Level 8

                        You didn't follow my directions.  You are still pulling 3 separate recordsets. in the second example, I gave you code to pull a single recordset that selects for all three variables.

                        • 9. Re: Any ideas on how to incorporate a simple database into my website?
                          adamjewsbury20 Level 1

                          I tried the second example, and it caused syntax errors?

                          • 11. Re: Any ideas on how to incorporate a simple database into my website?
                            adamjewsbury20 Level 1

                            <?php require_once('../Connections/survey.php'); ?>

                            <?php

                            if (!function_exists("GetSQLValueString")) {

                            function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")

                            {

                              if (PHP_VERSION < 6) {

                                $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

                              }

                             

                             

                              $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

                             

                             

                              switch ($theType) {

                                case "text":

                                  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                                  break;   

                                case "long":

                                case "int":

                                  $theValue = ($theValue != "") ? intval($theValue) : "NULL";

                                  break;

                                case "double":

                                  $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";

                                  break;

                                case "date":

                                  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";

                                  break;

                                case "defined":

                                  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;

                                  break;

                              }

                              return $theValue;

                            }

                            }

                            $colname_Brand = "%";

                            if (isset($_GET['Brand'])) {

                              $colname_Brand = $_GET['Brand'];

                            }

                            $colname_Price = "%";

                            if (isset($_GET['Price'])) {

                              $colname_Price = $_GET['Price'];

                            }

                            $colname_Rating = "%";

                            if (isset($_GET['Rating'])) {

                              $colname_Rating = $_GET['Rating'];

                            }

                            mysql_select_db($database_survey, $survey);

                            $query_Results = "SELECT * FROM ideal WHERE Brand LIKE $colname_Brand AND Price LIKE $colname_Price AND Rating LIKE $colname_Rating));

                            $Results = mysql_query($query_Results, $survey) or die(mysql_error());

                            $row_Results = mysql_fetch_assoc($Results);

                            $totalRows_Results = mysql_num_rows($Results);

                             

                             

                            ?>

                             

                             

                            <!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>

                            <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

                            <title>Search Results</title>

                            </head>

                             

                             

                            <body>

                             

                             

                            <h1> Survey results</h1>

                            <?php

                                      $temp = $totalRows_Results;

                                      if ($temp == 0) { echo "No Records Found"; }

                            ?>

                            <table width="200" border="1">

                              <tr>

                                <td>id</td>

                                <td>Brand</td>

                                <td>Name</td>

                                <td>Price</td>

                                <td>Rating</td>

                                <td>Purpose</td>

                              </tr>

                              <?php do { ?>

                              <tr>

                                <td><?php echo $row_Results['id']; ?></td>

                                <td><?php echo $row_Results['Brand']; ?></td>

                                <td><?php echo $row_Results['Name']; ?></td>

                                <td><?php echo $row_Results['Price']; ?></td>

                                <td><?php echo $row_Results['Rating']; ?></td>

                                <td><?php echo $row_Results['Purpose']; ?></td>

                              </tr>

                              <?php } while ($row_Results = mysql_fetch_assoc($totalRowResults)); ?>

                            </table>

                             

                             

                            <br />

                            <a href="survey.html">Back to survey</a>

                            </body>

                            </html>

                            <?php

                            mysql_free_result($Results);

                            ?>

                             

                             

                            The bold and underlined lines have syntax errors, don't know what that means or how to fix it?

                            • 12. Re: Any ideas on how to incorporate a simple database into my website?
                              MurraySummers Level 8

                              There are no syntax errors in those lines.  Are you seeing these errors in DW, or in the W3 Validator? 

                               

                              Can you give us a link to the live page?