Page 1 of 2

php query

Posted: Sat Nov 26, 2011 2:07 pm
by someone2088
Hi,

I am developing a website using XHTML and PHP forms to allow a user to query a Postgre SQL database table. The database table contains a list of games, which the user should be able to view and query, in order to display just the games between any price range that the user specifies.

Currently, I have three pages: index.php, home.php and gamesByPrice.php

When the user initially browses to the website, they are taken to the index.php page, where they are asked to log in. Once they have logged in, they are taken to the home.php page, which displays a welcome message, and tells the user how many games there are currently stored in the database table, also displaying a full list of those games, along with their price and description in an XHTML table.

I then have a simple form, which the user can use to search for any games between any price range that they specify. When I enter values into the minimum and maximum price range text boxes, I am then taken to the gamesByPrice.php page, which should display the search results, currently however, it is just returning the error message below:

Code: Select all

if(!$dbconn){ die('Could not connect: ' . pg_error()); } 1Welcome !!
Warning: pg_query() [function.pg-query]: Query failed: ERROR: column "minprice" does not exist LINE 2: WHERE price>=minPrice && price<=maxPrice ^ in /berw/ugrad1/base/e/eef8/public_html/cs25010/gamesByPrice.php on line 42
Error in query: SELECT title, price, description FROM CSGames WHERE price>=minPrice && price<=maxPrice.ERROR: column "minprice" does not exist LINE 2: WHERE price>=minPrice && price<=maxPrice ^
The code for my home.php page is displayed below:

Code: Select all

<?php 
session_start(); 
	// retrieve stored session data
	$_SESSION['userName']=$_POST['userName'];
?>


<html>
<head>
<title>Home</title>
</head>
<body>
<h1>Home</h1>

<?php
	// retrieve session data
echo "Welcome ".$_SESSION['userName']."!"; 
?>!

<p><br /></p>

<?php
	echo extension_loaded('pgsql');
	
	$conn_string = "host=***** port=**** dbname=***** user=***** password=*****";
	$dbconn = pg_connect($conn_string);
	
	if(!$dbconn){
		die('Could not connect: ' . pg_error());
		}
		
	// generate and execute a simple query to check that the php script is connecting
	// to the database, and that SQL queries will return required data
	$query = "SELECT * FROM CSGames";
	$result = pg_query($dbconn, $query) or die("Error in query: $query." . pg_last_error($dbconn));
	// Get the number of rows in the resultset
	$rows = pg_num_rows($result);
	echo "\n There are currently $rows games in the database.";
?>
<p><br /></p>	

<h1>Search for games by price: </h1>
<p>Enter price range of games you would like to search for:</p>

<form action="gamesByPrice.php" method="post">

Miniumum price: <input type="text" name="minPrice" />
Maximum price: <input type="text" name="maxPrice" />
<input type="submit" value="Search" />
</form>

<?php
	// This stores the values that the user entered for min and max price, so that
	// a user can search for games by any particular price range.
	$_SESSION ['minPrice']="minPrice";
	$_SESSION ['maxPrice']="maxPrice";	
?>
 

<?php
	
	// query to retrieve titles of all games from database, with their price and description
	$gameTitlesQuery = "SELECT title, price, description FROM CSGames";
	$gameTitlesQueryResult = pg_query($dbconn, $gameTitlesQuery) or die("Error in query: $gameTitlesQuery." . pg_last_error($dbconn));
	?>

	
<?php
	echo '<div id="Games">';
	echo '<table id="Games" border="1">';
	while ($a=pg_fetch_row($gameTitlesQueryResult)){
		echo '<tr>';
		for ($i=0; $i<pg_num_fields($gameTitlesQueryResult); $i++){
			echo '<td>'.htmlspecialchars($a[$i], ENT_QUOTES).'</ td>';
		}
	echo "<td><input type='checkbox' name='selectGame' value='{$a['refnumber']}' /></ td>
	</ tr>";
	}
	echo '</table></ div>';
?>


</body>
</html>
and this is the code for my gamesByPrice.php page:

Code: Select all

<?php 
session_start(); 
	// retrieve stored session data
	$_SESSION['userName']=$_POST['userName'];
	$_SESSION['minPrice']=$_POST['minPrice'];
	$_SESSION['maxPrice']=$_POST['maxPrice'];
?>


<html>
<head>
<title>Games Search By Price</title>
</head>
<body>
<h1>Search Results</h1>

if(!$dbconn){
		die('Could not connect: ' . pg_error());
		}

<?php
	echo extension_loaded('pgsql');
	
	$conn_string = "host=***** port=**** dbname=***** user=***** password=*****";
	$dbconn = pg_connect($conn_string);
	
	

	// retrieve session data
echo "Welcome ".$_SESSION['userName']."!"; 
?>!

<?php

	// To get the query below to work, I will need to store the values the user entered
	// for minimum and maximum price in PHP variables, and then retrieve them on 
	// this page
	
	// query to retrieve titles of all games from database, whose price is between a certain range, with their price and description
	$gameTitlesByPriceQuery = "SELECT title, price, description FROM CSGames
		WHERE price>=minPrice && price<=maxPrice";
	$gameTitlesByPriceQueryResult = pg_query($dbconn, $gameTitlesByPriceQuery) or die("Error in query: $gameTitlesByPriceQuery." . pg_last_error($dbconn));
	?>

<p><br /></p>
If someone could point out to me where I'm going wrong, I would be very grateful!

Re: php query

Posted: Sat Nov 26, 2011 2:59 pm
by Celauran
Looks like you forgot the $ before your variables in the query string. Try this:

Code: Select all

$gameTitlesByPriceQuery = "SELECT title, price, description FROM CSGames
                WHERE price >= {$minPrice} && price <= {$maxPrice}";

Re: php query

Posted: Sat Nov 26, 2011 3:04 pm
by someone2088
Thanks for your reply.

I've made the changes you suggested, and I'm now getting a slightly different error messsage:
Warning: pg_query() [function.pg-query]: Query failed: ERROR: operator does not exist: && numeric LINE 2: WHERE price>= && price<= ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. in /berw/ugrad1/base/e/eef8/public_html/cs25010/gamesByPrice.php on line 43
Error in query: SELECT title, price, description FROM CSGames WHERE price>= && price<=.ERROR: operator does not exist: && numeric LINE 2: WHERE price>= && price<= ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
I'm not too sure what it means by needing to add explicit type casts- any suggestions?

Thanks again.

Re: php query

Posted: Sat Nov 26, 2011 3:27 pm
by Celauran
Replace && with AND

Also, it looks like your variables may not contain values. I recommend echoing the query to make sure it's right.

Re: php query

Posted: Mon Nov 28, 2011 9:52 am
by someone2088
I've tried replacing && with AND, but I'm still getting the same error- just that this time, it says AND instead of &&:
Warning: pg_query() [function.pg-query]: Query failed: ERROR: operator does not exist: numeric >= LINE 2: WHERE price>= AND price<= ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. in /berw/ugrad1/base/e/eef8/public_html/cs25010/gamesByPrice.php on line 43
Error in query: SELECT title, price, description FROM CSGames WHERE price>= AND price<=.ERROR: operator does not exist: numeric >= LINE 2: WHERE price>= AND price<= ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
It seems to be complaining about the types- would this be because the input types of my minPrice and maxPrice variables are text rather than decimal?

The code I used the store the values entered into the text boxes in the search form on the home page was this:

Code: Select all

<form action="gamesByPrice.php" method="post">

Miniumum price: <input type="text" name="minPrice" />
Maximum price: <input type="text" name="maxPrice" />
<input type="submit" value="Search" />
</form>
Do I need to change the input types to decimal, if so, do I just use the word 'decimal' in place of where I've used 'text'?

Re: php query

Posted: Mon Nov 28, 2011 9:59 am
by Celauran
someone2088 wrote:
Warning: pg_query() [function.pg-query]: Query failed: ERROR: operator does not exist: numeric >= LINE 2: WHERE price>= AND price<= ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. in /berw/ugrad1/base/e/eef8/public_html/cs25010/gamesByPrice.php on line 43
Error in query: SELECT title, price, description FROM CSGames WHERE price>= AND price<=.ERROR: operator does not exist: numeric >= LINE 2: WHERE price>= AND price<= ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
It seems to be complaining about the types- would this be because the input types of my minPrice and maxPrice variables are text rather than decimal?
It looks to me like it's complaining because they're missing altogether. Have you tried echoing the query as I suggested? Looks like your variables contain nothing.

Re: php query

Posted: Mon Nov 28, 2011 10:08 am
by someone2088
I've just tried echoing the minPrice and maxPrice variables above where the query results should be displayed, and it has printed out the values that I gave the variables.

Re: php query

Posted: Mon Nov 28, 2011 11:05 am
by mikosiko

Code: Select all

        // query to retrieve titles of all games from database, whose price is between a certain range, with their price and description
        $gameTitlesByPriceQuery = "SELECT title, price, description FROM CSGames
                WHERE price>=minPrice && price<=maxPrice";
I don't see your $_SESSION[] variables in the query

Re: php query

Posted: Tue Nov 29, 2011 7:25 am
by someone2088
Here's the full section of code where I'm trying to query the database table:

Code: Select all

<?php

	// check to see that minPrice and maxPrice have been stored in PHP session variables
	
	echo $_SESSION['minPrice'];
	echo $_SESSION['maxPrice'];

	// To get the query below to work, I will need to store the values the user entered
	// for minimum and maximum price in PHP variables, and then retrieve them on 
	// this page
	
	// query to retrieve titles of all games from database, whose price is between a certain range, with their price and description
	$gameTitlesByPriceQuery = "SELECT title, price, description FROM CSGames
		WHERE price>={$minPrice} AND price<={$maxPrice}";
	$gameTitlesByPriceQueryResult = pg_query($dbconn, $gameTitlesByPriceQuery) or die("Error in query: $gameTitlesByPriceQuery." . pg_last_error($dbconn));
	?>
When I view the page in the browser, having clicked search on the previous page, the first two echo statements print out the values that I entered in the text boxes on the previous page, so the $_SESSION variables are definately storing the user input.

Apparently the problem is something to do with a change in the latest version of PostgreSQL- it treats anything you put in the text box as a 'string' type, and when you send it to PostgreSQL, it can't compare it as a number. I need to do a type cast, either in the SQL, or in the PHP, before seding it to the SQL. Could someone explain to me how I would do this?

Re: php query

Posted: Tue Nov 29, 2011 7:30 am
by maxx99
Im not familiar with PostgreSQL but like you've said, you can always cast your variables to int-s, float-s or whatever you want :)
http://php.net/manual/en/language.types ... ggling.php

Example #1 floatval() Example

Code: Select all

<?php
$var = '122.34343';
$float_value_of_var = floatval($var);// $float_value_of_var = 122.34343 its a float now, not string
?>
And some more examples
http://www.php.net/manual/en/language.t ... conversion

Re: php query

Posted: Tue Nov 29, 2011 7:54 am
by someone2088
Ok, just had a quick look at trying the type casting, but don't seem to be getting anywhere with it... I tried doing the casting between the echo statements and the $gameTitlesByPriceQuery... but I've just noticed that the error that is displayed in the browser says that I might need to add the type casts on the $gameTitlesByPriceQueryResult line... not too sure how to go about doing that- any ideas?

Re: php query

Posted: Tue Nov 29, 2011 8:00 am
by maxx99
:lol: when im looking at this for second time...
Type shouldn't matter because you put it in the middle of query string anyway

Re: php query

Posted: Tue Nov 29, 2011 8:19 am
by maxx99
Could you post var_dump($gameTitlesByPriceQuery); ?

Re: php query

Posted: Tue Nov 29, 2011 10:15 am
by Celauran
someone2088 wrote:Here's the full section of code where I'm trying to query the database table:

Code: Select all

<?php

	// check to see that minPrice and maxPrice have been stored in PHP session variables
	
	echo $_SESSION['minPrice'];
	echo $_SESSION['maxPrice'];

	// To get the query below to work, I will need to store the values the user entered
	// for minimum and maximum price in PHP variables, and then retrieve them on 
	// this page
	
	// query to retrieve titles of all games from database, whose price is between a certain range, with their price and description
	$gameTitlesByPriceQuery = "SELECT title, price, description FROM CSGames
		WHERE price>={$minPrice} AND price<={$maxPrice}";
	$gameTitlesByPriceQueryResult = pg_query($dbconn, $gameTitlesByPriceQuery) or die("Error in query: $gameTitlesByPriceQuery." . pg_last_error($dbconn));
	?>
When I view the page in the browser, having clicked search on the previous page, the first two echo statements print out the values that I entered in the text boxes on the previous page, so the $_SESSION variables are definately storing the user input.
Are you ever setting $minPrice = $_SESSION['minPrice']? You need to either do that, or use $_SESSION['minPrice'] in your query string.

Re: php query

Posted: Tue Nov 29, 2011 10:37 am
by mikosiko
Celauran wrote: Are you ever setting $minPrice = $_SESSION['minPrice']? You need to either do that, or use $_SESSION['minPrice'] in your query string.
someone already told him that few posts ago :wink: