Help needed... search a MySQL database and format the result (Quick Order Form)

Legacy Dynamo
1

Hi everyone and Happy New Year to one and all!!

I’m having a bit of trouble getting this to work.

What I am trying to achieve (but failing!) is to implement a quick order form on a page whereby the customer fills in the part number, this is then checked with the MySQL database which sends back the product details, description, price etc and a buy link so that they can click and buy the item.

I have the database working so that you select the products you want to view by using the css drop down menus. I want to use the same database and do a search by part number that will let the customer quickly order a product by the part number, checking with the database that it is a valid part.

I’ve got a simple form that does a search on the database, but I seem to have hit a brick wall because no matter how I configure the query I keep getting this error:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource…

I’m using the MySQL Get Records action, as used in the Axiom Widgets tutorial (it’s working for the menu items), it just doesn’t seem to work for the search.

Have a look at this…

http://www.cotswoldengineeringsupplies.co.uk/2008-dev/quickorderform.php

This is the query in the “Build SQL here” in the Action on the results page.

SELECT cesproducts.*, cesprices.price FROM cesproducts, cesprices
WHERE partnumber = ‘$qspartnumber’
AND cesproducts.partnumber = cesprices.partnumber

The Form Setup on the Quick Order Form page is set to “POST” and the Action to the results page.

There is obviously something I’m doing wrong, but I can’t seem to find what it is, maybe I’ll have to attack this from a different angle to get it working. I also need to validate the search before it is sent.

Thank you in anticipation, and apologies for the long post.

Regards
SteveB

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

2

This could be one of a few different things, try using:

(SELECT cesproducts.*, cesprices.price FROM cesproducts, cesprices
WHERE partnumber = ‘$qspartnumber’ AND cesproducts.partnumber =
cesprices.partnumber) or die ('Error: '.mysql_error ());

This will probably give you a more informative error and make it
easier to debug.

Let us know what the error is after making the above alteration.

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

3

I’ve just tried that, but it doesn’t come up with any other error, only this one:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/cotswol/public_html/2008-dev/orderresult.php on line 25

which is the same error I’ve been getting all along!

May be a way round the problem would be to re do the pages but not use the MySQL Get Records action, and use mark-up and includes instead.

SteveB.

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

4

To be honest I don’t use actions for my PHP and Database stuff with
Freeway, I just write the scripts and then include them, I find this
easier to maintain but that’s not to say that using actions is not a
viable way to go on this.

I have found that error can be returned when the table name is not
spelt properly or a connection is not being made to the database.

Do you have any other query code that you can show us? i.e. the rest
of it.

On Jan 3, 2009, at 10:55 PM, SteveB wrote:

I’ve just tried that, but it doesn’t come up with any other error,
only this one:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL
result resource in /home/cotswol/public_html/2008-dev/
orderresult.php on line 25

which is the same error I’ve been getting all along!

May be a way round the problem would be to re do the pages but not
use the MySQL Get Records action, and use mark-up and includes
instead.

SteveB.

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

5

Steve,
Not trying to teach you how to suck eggs but If you where going to set
the PHP into a script and including it rather than using actions then
the query part of the script could be structured as follows:

if ($qspartnumber) { // If it has a text value, if it needs to be
numberic then you could use: is_numeric($qspartnumber)

// Include the connection script
require($DA['path']['inc_dir'] . 'dbconnect.php');

$my_qry = ("SELECT `cesproducts`.*, `cesprices`.`price` FROM

cesproducts, cesprices WHERE partnumber = ‘$qspartnumber’ AND
cesproducts.partnumber = cesprices.partnumber");
$my_rslt = @mysql_query($my_qry) or die ('File: ’ .
end(explode(“/”,FILE)) . '
Query line: ’ . LINE . '<br /

Query: ’ . $my_qry . '
Mysql error: ’ . mysql_error());
$num_rows = @mysql_num_rows($my_rslt);

if ($num_rows >0) {

		while ($row = mysql_fetch_array($my_rslt)) {
		// Get the data
	}

} else {
	// Handle this some informative way for the user
}
mysql_close($dbc);

} else {
// Handle this some informative way for the user
}

Now with the above I would generally get an informative responce when
there is a problem and so does the user if the input or result is not
as expected if you follow through the ‘Handle this some informative
way for the user’. You could even throw in a debug flag set in your
basic config file that is included on each script, make it when set to
true then the bit after ‘or die’ will be seen and when in production
set to false so any user doesn’t get to see the more informative
errors. A very basic way of doing this would be:

if ($debug) {
$my_rslt = @mysql_query($my_qry) or die ('File: ’ .
end(explode(“/”,FILE)) . '
Query line: ’ . LINE . '<br /

Query: ’ . $my_qry . '
Mysql error: ’ . mysql_error());
} else {
$my_rslt = @mysql_query($my_qry);
}

If you where to use the above code then make sure that none of it is
wrapped (probably is) and maybe best to run it through BBEdit to check
for any little black balls ‘show invisibles’ and replace them with a
space, it shouldn’t have any but just in case.

HTH
Mike

On Jan 3, 2009, at 10:55 PM, SteveB wrote:

I’ve just tried that, but it doesn’t come up with any other error,
only this one:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL
result resource in /home/cotswol/public_html/2008-dev/
orderresult.php on line 25

which is the same error I’ve been getting all along!

May be a way round the problem would be to re do the pages but not
use the MySQL Get Records action, and use mark-up and includes
instead.

SteveB.

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

6

Thanks Mike,

Just to test the original Actions I created 2 pages, a search and a result page with just the bare minimum action etc, and the error produced was the same.

I’m going to recreate the page using includes, which hopefully will work.

By the way, when posting a form is it usual to use $_POST to retrieve the variable sent by the form POST.
I’ve been using $qspartnumber = $_POST[‘qspartnumber’];

as the input that gets the query from the form. It is being passed to the results page but I dont think it’s getting to the query part.

I’ll let you know how I get on, thanks for the help, it’s much appreciated.

Regards,
Steve

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

7

The $_POST and $_GET “superglobal” arrays are the preferred way to
access form and URL variables in PHP. There was a time when you could
simply post a form and immediately access $yourformelement directly
without declaring where it came from. This was and is a security
problem waiting to happen, so it got closed off by default in PHP4 and
then welded shut in PHP5.

If you are using the Actions, I can’t help you much on how your query
gets structured, but it is a very good idea to “sanitize” your inputs
when using public (untrustworthy) data to access a database.

$sql = 'SELECT * FROM `tablename` WHERE id = "' . (int) $_POST['id'] .  
'"';

What’s happening in here is we are deliberately “casting” the
untrustworthy input to an integer, which will force any non-numeric
content to be thrown out. So if someone inserts a SQL escape and tries
to change the query to something which will dump all the passwords
into the global scope, you won’t have to worry about that at all.

Another thing you can do to user input is to use the
mysql_real_escape_string() function on any string you are going to
add to a query. Here’s how this can look in practice:

$search_term = mysql_real_escape_string($strSearchTerm);
$sql = "SELECT *, MATCH (`from`,`subject`,`plain_text`) AGAINST  
('$search_term' IN BOOLEAN MODE) AS `_score` FROM `message` WHERE  
MATCH (`from`,`subject`,`plain_text`) AGAINST ('$search_term' IN  
BOOLEAN MODE) ORDER BY _score DESC");

This is the query generator from this forum’s previous search engine
(before we upgraded to Sphinx). The user input is part of the query in
several places, so we run the escape code against it once and then
inject that into the query.

This function assumes that you have a currently-open connection to a
MySQL database, and uses that connection to have the mysql server do
the escaping process itself, following whatever rules of SQL that it
currently understands. This is the most secure way to avoid having
your database messed with, since you aren’t relying on your own
understanding of the SQL standard.

Walter

On Jan 4, 2009, at 2:49 PM, SteveB wrote:

Thanks Mike,

Just to test the original Actions I created 2 pages, a search and a
result page with just the bare minimum action etc, and the error
produced was the same.

I’m going to recreate the page using includes, which hopefully will
work.

By the way, when posting a form is it usual to use $_POST to
retrieve the variable sent by the form POST.
I’ve been using $qspartnumber = $_POST[‘qspartnumber’];

as the input that gets the query from the form. It is being passed
to the results page but I dont think it’s getting to the query part.

I’ll let you know how I get on, thanks for the help, it’s much
appreciated.

Regards,
Steve

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

8

Thanks, Mike and Walter,

I’ve been doing some more delving around on the original form page, swapping the ‘partnumber’ with ‘prodtitle’, ‘description’ etc and found that even if I put the partnumber in the search on the Freeway page it still produces the error, whereas if I use ‘prodtitle’ or ‘description’ and enter that into the form page and submit it then the results page produces the correct items and prices from the database.

I wonder if the problem could be that there is a join on the two databases used (they’re joined by ‘partnumber’) and this is what is searched for on the form.

SELECT cesproducts.*, cesprices.price FROM cesproducts, cesprices
WHERE partnumber = ‘$search_term’
AND cesproducts.partnumber = cesprices.partnumber

I have found that I can search for the product by partnumber if I use either the cesproducts database, which has all the product details - partnumber, description, product title, image etc., or the cesprices database which has all the prices, (it has two fields, partnumber and price) they are joined together by the partnumber field.

I can get all the product information I need if I do two separate searches, all I need to do is to get these two searches into one line and not the two separate lines I have at the moment.

Back to the books to see if they have any solutions.

Thanks for the help, it’s been invaluable for me and has helped me to understand more fully how Freeway and php/MySQL can be integrated together.

Regards Steve

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

9

What about telling the query the table that partnumber is in (just
after the WHERE clause).

Does that do it?

On Jan 5, 2009, at 5:38 PM, SteveB wrote:

Thanks, Mike and Walter,

I’ve been doing some more delving around on the original form page,
swapping the ‘partnumber’ with ‘prodtitle’, ‘description’ etc and
found that even if I put the partnumber in the search on the Freeway
page it still produces the error, whereas if I use ‘prodtitle’ or
‘description’ and enter that into the form page and submit it then
the results page produces the correct items and prices from the
database.

I wonder if the problem could be that there is a join on the two
databases used (they’re joined by ‘partnumber’) and this is what is
searched for on the form.

SELECT cesproducts.*, cesprices.price FROM cesproducts, cesprices
WHERE partnumber = ‘$search_term’
AND cesproducts.partnumber = cesprices.partnumber

I have found that I can search for the product by partnumber if I
use either the cesproducts database, which has all the product
details - partnumber, description, product title, image etc., or the
cesprices database which has all the prices, (it has two fields,
partnumber and price) they are joined together by the partnumber
field.

I can get all the product information I need if I do two separate
searches, all I need to do is to get these two searches into one
line and not the two separate lines I have at the moment.

Back to the books to see if they have any solutions.

Thanks for the help, it’s been invaluable for me and has helped me
to understand more fully how Freeway and php/MySQL can be integrated
together.

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

10

Thanks Mike for the quick reply…
I’ve just tried that, adding cesproducts. to the partnumber after the WHERE clause, but the error comes back.

What I have managed to do as I can only get the results separately is to have the selected price from the cesprices database put into a separate string, and adding this to the other result (without the price on that database) as you’re seaching for the same thing on both databases the result is the correct product with the correct price.

Strange that doing it the way you’d think it should work doesn’t. I still can’t understand why it shouldn’t work.

Oh well, c’est la vie…

Regards

Steve.

On 5 Jan 2009, 3:46 pm, Mike B wrote:

What about telling the query the table that partnumber is in (just
after the WHERE clause).

Does that do it?

On Jan 5, 2009, at 5:38 PM, SteveB wrote:

Thanks, Mike and Walter,

I’ve been doing some more delving around on the original form page,
swapping the ‘partnumber’ with ‘prodtitle’, ‘description’ etc and
found that even if I put the partnumber in the search on the Freeway
page it still produces the error, whereas if I use ‘prodtitle’ or
‘description’ and enter that into the form page and submit it then
the results page produces the correct items and prices from the
database.

I wonder if the problem could be that there is a join on the two
databases used (they’re joined by ‘partnumber’) and this is what is
searched for on the form.

SELECT cesproducts.*, cesprices.price FROM cesproducts, cesprices
WHERE partnumber = ‘$search_term’
AND cesproducts.partnumber = cesprices.partnumber

I have found that I can search for the product by partnumber if I
use either the cesproducts database, which has all the product
details - partnumber, description, product title, image etc., or the
cesprices database which has all the prices, (it has two fields,
partnumber and price) they are joined together by the partnumber
field.

I can get all the product information I need if I do two separate
searches, all I need to do is to get these two searches into one
line and not the two separate lines I have at the moment.

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

11

Steve,
I just ran your following query as you have it and I got a syntax
error…

$my_qry = ("SELECT cesproducts .*, cesprices .price FROM cesproducts ,  
cesprices  WHERE cesprices .partnumber = '$search_term' AND  
cesproducts .partnumber = cesprices. partnumber");

but when I change it to the following it returned results:

$my_qry = ("SELECT `cesproducts`.*, `cesprices`.`price` FROM  
`cesproducts`, `cesprices` WHERE `cesprices`.`partnumber` =  
'$search_term' AND `cesproducts`.`partnumber` =  
`cesprices`.`partnumber`");

OK I changed the table names and field names to match for one of my
databases and the query returned the expected results, try adding the
back tick (`) around the table and field names (not on the asterix *).
I don’t know why that would be as it should also run without the back
ticks! all they do is tell MySQL that there is a table or field name
coming in rather than having to work that out for itself. Unless there
is a typo in one of the table or field names!

On further investigation I discovered an oddity in that if I use the
back ticks up to ("SELECT cesproducts .*, cesprices .price FROM
cesproducts then the query runs but not having them on any of the
table names in this part of the string throws an error… weird but
that is what happened in my test, so add the back ticks as I have in
my version of the query and see if that helps (Note the difference
between ` and ’ as one does not replace the other and one used where
the other should be will throw an error).

HTH
Mike

On Jan 5, 2009, at 6:36 PM, SteveB wrote:

Thanks Mike for the quick reply…
I’ve just tried that, adding cesproducts. to the partnumber after
the WHERE clause, but the error comes back.

What I have managed to do as I can only get the results separately
is to have the selected price from the cesprices database put into a
separate string, and adding this to the other result (without the
price on that database) as you’re seaching for the same thing on
both databases the result is the correct product with the correct
price.

Strange that doing it the way you’d think it should work doesn’t. I
still can’t understand why it shouldn’t work.

Oh well, c’est la vie…

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

12

Mike, That’s brilliant!!

It works a treat.

Like you I still don’t understand why MySQL throws up an error, I checked the spelling I don’t know how many times, and checked the syntax of the query, and like you I found that the bit that threw the error is the the bit after the AND. I found out, if you split the query into two parts each will run successfully, but combing them caused the error.

Thanks again for your patience and expertise it is very much appreciated.

Well, that’s another tick on the list of jobs to do… (and probably the most challenging!!)

Best wishes,

Steve.

Steve, I just ran your following query as you have it and I got a syntax error…

$my_qry = (“SELECT cesproducts .*, cesprices .price FROM cesproducts ,
cesprices WHERE cesprices .partnumber = ‘$search_term’ AND
cesproducts .partnumber = cesprices. partnumber”);

but when I change it to the following it returned results:

$my_qry = (“SELECT cesproducts.*, cesprices.price FROM
cesproducts, cesprices WHERE cesprices.partnumber =
‘$search_term’ AND cesproducts.partnumber =
cesprices.partnumber”);

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options