Solved

Querying a MySQL Table with Multiple URL Parameters

Posted on 2008-10-20
8
294 Views
Last Modified: 2013-12-13
Hello, I'm sure there is a simple solution to this, but I've been tearing my hair out for the last couple of hours, I have a form that generates a URL with multiple variables:

hostingsearch.php?Type=0&PriceGBP=10&StorageGB=10&DataTransferGB=10&submit=search

I cant for the life of me get the variables included into a MySQL query.

The parameters in the URL are exactly the same as the Fields in my Table that I am querying.

I want to retrieve and display information below a certain price, above a certain storage and above a certain bandwidth.

I've been playing with the query for hours and it's a bit of a mess - I'll post it below.

N.B. The urls work for individual queries created in DW, but not when I combine them.

Many thanks in advance for your help.

Betch
mysql_select_db($database_webhosting, $webhosting);

$query_hostingsearch = ("SELECT `Host`, Package, Rating, Storage, `Data Transfer`, `Included Domains`, Setup, Price 

FROM UKshared WHERE Type = %s", GetSQLValueString($colname_Type, "double")  AND PriceGBP <= %s", GetSQLValueString($colname_PriceGBP, "double")  AND StorageGB >= %s", GetSQLValueString($colname_StorageGB, "double")  AND DataTransferGB >= %s", GetSQLValueString($colname_DataTransferGB, "double") ORDER BY PriceGBP ASC ");

$query_limit_hostingsearch = sprintf("%s LIMIT %d, %d", $query_hostingsearch, $startRow_hostingsearch, $maxRows_hostingsearch);

$hostingsearch = mysql_query($query_limit_hostingsearch, $webhosting) or die(mysql_error());

$row_hostingsearch = mysql_fetch_assoc($hostingsearch);

Open in new window

0
Comment
Question by:betch
  • 4
  • 4
8 Comments
 
LVL 82

Expert Comment

by:hielo
ID: 22761869
try:

<?php
mysql_select_db($database_webhosting, $webhosting);
$query_limit_hostingsearch = sprintf("SELECT `Host`, `Package`, `Rating`, `Storage`, `Data Transfer`, `Included Domains`, `Setup`, `Price` FROM `UKshared` WHERE `Type` = %s  AND `PriceGBP` <= %s  AND `StorageGB` >= %s  AND `DataTransferGB` >= %s ORDER BY PriceGBP ASC  LIMIT %d, %d", 
					GetSQLValueString($colname_Type, "double"),
					GetSQLValueString($colname_PriceGBP, "double"), 
					GetSQLValueString($colname_StorageGB, "double"), 
					GetSQLValueString($colname_DataTransferGB, "double"), 
					$startRow_hostingsearch, 
					$maxRows_hostingsearch
					);
$hostingsearch = mysql_query($query_limit_hostingsearch, $webhosting) or die(mysql_error());
$row_hostingsearch = mysql_fetch_assoc($hostingsearch);
?>

Open in new window

0
 

Author Comment

by:betch
ID: 22762256
Thanks hielo, I seem to be getting somewhere now however it returns 'query was empty'.

I'll post all of hosting search below.
<?php require_once('Connections/webhosting.php'); ?>

<?php

if (!function_exists("GetSQLValueString")) {

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

{

  $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;

}

}
 

$maxRows_hostingsearch = 10;

$pageNum_hostingsearch = 0;

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

  $pageNum_hostingsearch = $_GET['pageNum_hostingsearch'];

}

$startRow_hostingsearch = $pageNum_hostingsearch * $maxRows_hostingsearch;
 

mysql_select_db($database_webhosting, $webhosting);

$query_limit_hostingsearch = sprintf("SELECT `Host`, `Package`, `Rating`, `Storage`, `Data Transfer`, `Included Domains`, `Setup`, `Price` FROM `UKshared` WHERE `Type` = %s  AND `PriceGBP` <= %s  AND `StorageGB` >= %s  AND `DataTransferGB` >= %s ORDER BY PriceGBP ASC  LIMIT %d, %d", 

                                        GetSQLValueString($colname_Type, "double"),

                                        GetSQLValueString($colname_PriceGBP, "double"), 

                                        GetSQLValueString($colname_StorageGB, "double"), 

                                        GetSQLValueString($colname_DataTransferGB, "double"), 

                                        $startRow_hostingsearch, 

                                        $maxRows_hostingsearch

                                        );

$hostingsearch = mysql_query($query_hostingsearch, $webhosting) or die(mysql_error());

$row_hostingsearch = mysql_fetch_assoc($hostingsearch);

$totalRows_hostingsearch = mysql_num_rows($hostingsearch);

?><!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>Untitled Document</title>

</head>
 

<body>

<table border="1">

  <tr>

    <td>Host</td>

    <td>Package</td>

    <td>Rating</td>

    <td>Storage</td>

    <td>Data Transfer</td>

    <td>Included Domains</td>

    <td>Setup</td>

    <td>Price</td>

  </tr>

  <?php do { ?>

    <tr>

      <td><?php echo $row_hostingsearch['Host']; ?></td>

      <td><?php echo $row_hostingsearch['Package']; ?></td>

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

      <td><?php echo $row_hostingsearch['Storage']; ?></td>

      <td><?php echo $row_hostingsearch['Data Transfer']; ?></td>

      <td><?php echo $row_hostingsearch['Included Domains']; ?></td>

      <td><?php echo $row_hostingsearch['Setup']; ?></td>

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

    </tr>

    <?php } while ($row_hostingsearch = mysql_fetch_assoc($hostingsearch)); ?>

</table>

</body>

</html>

<?php

mysql_free_result($hostingsearch);

?>

Open in new window

0
 
LVL 82

Expert Comment

by:hielo
ID: 22762686
it should be:
$hostingsearch = mysql_query($query_limit_hostingsearch, $webhosting) or die( mysql_error() );

Open in new window

0
 

Author Comment

by:betch
ID: 22762894
1 step closer, thanks so much for your help - hostingsearch.php now dispays the table but it is unpopulated/empty.

Could the problem be in the MySQL table?

Just checked MySQL and Type is "int" and DataTransferGB is "int"  - Apart from that it looks fine?!!

I'll post the form..
</div>

        <div id="divSearchPlans">

          <form action="hostingsearch.php" method="get" enctype="multipart/form-data" target="_parent">

            <table border="0" cellpadding="3" cellspacing="1">

              <tr>

                <td width="115" align="left" valign="top" nowrap="nowrap">Hosting Type :</td>

                <td align="right" valign="top"><select name="Type">

                  <option value="0">Shared Hosting</option>

                  <option value="1">Virtual Private Server</option>

                  <option value="2">Dedicated Server</option>

                  <option value="3">Reseller Hosting</option>

                    </select></td>

              </tr>

              <tr>

                <td width="115" align="left" valign="top" nowrap="nowrap">Monthly Price :</td>

                <td align="right" valign="top"><select name="PriceGBP" id="PriceGBP">

                  <option value="0" selected="selected">No Price Limit</option>

                  <option value="5">£5 or less</option>

                  <option value="10">£10 or less</option>

                  <option value="25">£25 or less</option>

                  <option value="50">£50 or less</option>

                  <option value="100">£100 or less</option>

                  <option value="100">£150 or less</option>

                  <option value="200">£200 or less</option>

                  <option value="300">£300 or less</option>

                  </select></td>

              </tr>

              <tr>

                <td width="115" align="left" valign="top" nowrap="nowrap">Storage :</td>

                <td align="right" valign="top"><select name="StorageGB" id="StorageGB">

                  <option value="" selected="selected">- - -</option>

                  <option value="1">At Least 1GB</option>

                  <option value="10">At Least 10GB</option>

                  <option value="25">At Least 25GB</option>

                  <option value="50">At Least 50GB</option>

                  <option value="100">At Least 100GB</option>

                  <option value="1000">At Least 1000GB</option>

                  <option value="0">Unlimited</option>

                  </select></td>

              </tr>

              <tr>

                <td width="115" align="left" valign="top" nowrap="nowrap">Transfer :</td>

                <td align="right" valign="top"><select name="DataTransferGB" id="DataTransferGB">

                  <option value="" selected="selected">- - -</option>

                  <option value="10">At Least 10GB</option>

                  <option value="500">At Least 500Gb</option>

                  <option value="1000">At Least 1000Gb</option>

                  <option value="5000">At Least 5000GB</option>

                  <option value="10000">At Least 10000GB</option>

                  <option value="15000">At Least 15000GB</option>

                  <option value="-1">Unlimited</option>

                  </select></td>

              </tr>

              

              

              <tr>

                <td colspan="2" align="right" valign="top" nowrap="nowrap">

                <input name="submit" type="submit" id="submit" value="search" /></td>

              </tr>

            </table>

          </form>

Open in new window

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 82

Expert Comment

by:hielo
ID: 22763128
On the four calls to:
GetSQLValueString(..., ...)

I don't see you defining the first variable anywhere for any of these calls. I see you have:
$maxRows_hostingsearch = 10;

$pageNum_hostingsearch = 0;
 

but I am NOT seeing:

$colname_Type=...;

$colname_PriceGBP=...;

$colname_StorageGB=...;

$colname_DataTransferGB=...;
 

(where ... is supposed to be some value) anywhere on top of the page. You need to provide values for those. Otherwise your query may be returning zero records.

Open in new window

0
 

Author Comment

by:betch
ID: 22765536
Thanks, I've added the 4 variables but if I populate them with numbers, my query just returns results for those numbers.

I need to populate my query with the URL variables.

Thanks again Betch
$maxRows_hostingsearch = 10;

$pageNum_hostingsearch = 0;

$colname_Type= [Type];

$colname_PriceGBP= ['PriceGBP'];

$colname_StorageGB= ['StorageGB'];

$colname_DataTransferGB= ['DataTransfer'];

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

  $pageNum_hostingsearch = $_GET['pageNum_hostingsearch'];

}

$startRow_hostingsearch = $pageNum_hostingsearch * $maxRows_hostingsearch;
 

mysql_select_db($database_webhosting, $webhosting);

$query_limit_hostingsearch = sprintf("SELECT `Host`, `Package`, `Rating`, `Storage`, `Data Transfer`, `Included Domains`, `Setup`, `Price` FROM `UKshared` WHERE `Type` = %s  AND `PriceGBP` <= %s  AND `StorageGB` >= %s  AND `DataTransferGB` >= %s ORDER BY PriceGBP ASC  LIMIT %d, %d", 

                                        GetSQLValueString($colname_Type, "int"),

                                        GetSQLValueString($colname_PriceGBP, "double"), 

                                        GetSQLValueString($colname_StorageGB, "double"), 

                                        GetSQLValueString($colname_DataTransferGB, "int"), 

                                        $startRow_hostingsearch, 

                                        $maxRows_hostingsearch

                                        );

Open in new window

0
 
LVL 82

Accepted Solution

by:
hielo earned 250 total points
ID: 22767097
Given this:
hostingsearch.php?Type=0&PriceGBP=10&StorageGB=10&DataTransferGB=10&submit=search

then you need to intialize your variables as follows:
$colname_Type=$_GET['Type'];
$colname_PriceGBP=$_GET['PriceBP'];
$colname_StorageGB=$_GET['StorageGB'];
$colname_DataTransferGB=$_GET['DataTransferGB'];
0
 

Author Closing Comment

by:betch
ID: 31507999
Thanks very much hielo, it works perfectly now!
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now