List:General Discussion« Previous MessageNext Message »
From:Steve Edberg Date:February 23 2001 2:46pm
Subject:Re: Mysql Table via php
View as plain text  
At 12:48 PM -0500 2/22/01, Joe and Nancy M wrote:
>I finally got my ISP to resolve the issues with connecting to my db. 
>I have a test table in the database named test.  I have 3 fields; 
>ID(20Char), PRICE(int),QTY(int).  I am using php (this is what my 
>ISP supports and suggested).
>
>I can connect using:
><?php
>
>$link = mysql_connect ("localhost.domainnamehere.com.", 
>"usernameherel", "passwordhere")
>
>or die ("Could not connect");
>
>
>?>
>
>I can draw the data out using this sample code:
>
><?php
>
>$query = "SELECT ID, PRICE, QTY
>
>FROM test
>
>where PRICE=3";
>
>$result = mysql_query ($query)
>
>or die ("Query failed");
>
># fetch rows in reverse order
>
>for ($i = mysql_num_rows ($result) - 1; $i >=0; $i--) {
>
>if (!mysql_data_seek ($result, $i)) {
>
>printf ("Cannot seek to row %d\n", $i);
>
>continue;
>
>}
>
>if(!($row = mysql_fetch_object ($result)))
>
>continue;
>
>printf ("%s %s %s<BR>\n", $row->ID, $row->PRICE, $row->QTY);
>
>}
>
>mysql_free_result ($result);
>
>?>
>
>
>
>1.  I can not seem to get the syntax correct to select where the 
>ID=text value.  I get parse errors with almost every scenario.  What 
>is the correct string to select where a character field is equal to 
>a value???


You need to single quote the value:

	$query = "SELECT ID, PRICE, QTY FROM test where id='text value'";


>2.  I primarily need to select one record from the table and display 
>the PRICE on the webpage and show a hyperlink "buyme" where QTY is 
>gt 0.  Does someone have a sample piece of php that will handle 
>this??


Well, this is a bit of a vague description, but here goes:

	$id = 'some value';

	$query = "SELECT id,price,qty FROM test WHERE id='$id'";
	$result = @mysql_query($query) or die('Arrrghhh....');

	if (mysql_num_rows($result) != 1)
	{
#	Do some error checking here: no record found or multiple 
records w/same id
	}
	else
	{
	echo '<a href="your_buyme_program.php?id=', urlencode($id), 
'&qty=1">Buy one now</a>';
	}


Also, there are a few things to note about the code you originally 
posted (above).

* you do not have an 'order by' in your sql statement. MySQL - and 
any relational database - will not necessarily return the results in 
any particular order without one. This will probably bollix up your 
fetching in 'reverse order'...there is no particular 'forward' order, 
so there ain't a REVERSE order.

* assuming you actually want the order to be by ID, why not let the 
database handle as much of the work as possible? It's faster at it 
than you & php:

	$query = "SELECT ID, PRICE, QTY FROM test where PRICE=3 order 
by id desc";
#	'desc' means to sort in descending order - ie, from z -> a

	$result = mysql_query ($query) or die ("Query failed");

	while ($d = mysql_fetch_object($result))
	{
	printf ("%s %s %s<BR>\n", $row->ID, $row->PRICE, $row->QTY);
	}
	mysql_free_result ($result);


>
>
>
>Thanks, I am running very short on time!!!!
>
>Joe.

-- 
+--- "They've got a cherry pie there, that'll kill ya" ------------------+
| Steve Edberg                           University of California, Davis |
| sbedberg@stripped                               Computer Consultant |
| http://aesric.ucdavis.edu/                  http://pgfsun.ucdavis.edu/ |
+-------------------------------------- FBI Special Agent Dale Cooper ---+
Thread
Mysql Table via phpJoe and Nancy M22 Feb
  • Re: Mysql Table via phpAtle Veka23 Feb
  • Re: Mysql Table via phpSteve Edberg23 Feb