Query a specific value from mysql using PHP variables

[mattclayb]

Hi,

I know some PHP but am a novice in mysql, I have a table of shipping prices, e.g.-

shipType ¦ shipCode1 ¦ shipCode2 ¦ shipCode3
---------¦-----------¦-----------¦-----------
EUCourier¦____3_____¦____4_____¦____6______
UKCourier¦____2_____¦____3_____¦____4______
UKFirstCl_¦____0.50__¦____0.75__¦____1______
USCourier¦____4_____¦____6_____¦____8______

I have the variables of shipType and shipCode produced by PHP, and want to use these to return a specific value from the table.

e.g. I want to write a query that will enable -

$shipType='EUCourier'; $shipCode='shipCode1';

to return the value '3'. I would appreciate any help anyone can give me,

Kind Regards,
Matt

[Blackie]

Welcome to WW mattclayb :-)

Very basic, you do something like this:

<?php

$db_host = "localhost";
$db_user = "user";
$db_pass = "password";
$db_name = "db_name";

$db = mysql_connect($db_host, $db_user, $db_pass) or die('Can\'t connect to database');

mysql_select_db($db_name, $db) or die('Unable to select database');

$query = "SELECT shipCode1 FROM replace_with_shiptype_table_name WHERE shipType='EUCourier'";

$row = mysql_query ($query, $db) or die('Can\'t run query');
echo $row[0];
?>

[mattclayb]

Hi, thanks for your help, I've ran the following query, and it returns 'Resource id #3' instead of an integer. I need to produce a figure from the table to add it to another figure in PHP.

Any ideas on how to return a usable integer?

$query = "SELECT '".$shipCode."' FROM shipping WHERE shipType='".$shipType."'";

$row = mysql_query ($query) or die('Can\'t run query');
echo $row[0];

[Blackie]

By the way, you can use following syntax:
$query = "SELECT $shipCode FROM shipping WHERE shipType='$shipType'";

I wonder if you have "Resource id #3" in the database or you just have integer "3" there? (I assume we still on the same example e.g. $shipType='EUCourier'; $shipCode='shipCode1'; )

[mattclayb]

Hi,

Sorry to be a pain,

yes it is the same table and variables we're talking about. But no matter what the variables are the same 'Resource id #3' is returned. I've checked my Variables are functioning correctly, any ideas?

Matt

[Blackie]

no pain no gain :-)

Try changing shipType to UKFirstCl and see what you get.

[Blackie]

You can also try changing to
$query = "SELECT $shipCode FROM shipping WHERE shipType LIKE '$shipType'";

but I think it should work with "="-sign as well.

[mcibor]

The problem lies within sql query.
I understand that $shipcode is integer = 3
Therefore you got yourself such query:
SELECT 3 FROM ...

The corrected query is
SELECT shipCode FROM shipping WHERE shipType='$shipType'";

Best regards
Michal Cibor