Select . . . Where

[timstring]

I have a database with two tables: "Pay Codes" and "Worksheet". Worksheet is an invoice of sorts containing 13 fields of pay codes in the fields of First, S1, S2, S3, . . .
"Pay Codes" has three fields: "Code", "Description", and "Price". I'm trying to write a PHP routine that takes "First", "S1", . . . from Worksheet and pulls the appropriate data from Pay Codes. What I'm aiming for is when a user enters "AC7" in the "First" field in Worksheet, MySQL returns the "Description" and "Price" for "Code" This is what I've cobbled together from www.w3schools.com/PHP/php_mysql_where.asp

<?php
$con = mysql_connect("localhost","root","");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("BTS", $con);

$sql="INSERT INTO Worksheet (Date, TechNum, Job, Account, Address, Equipment, First, S1, S2, S3, S4, S5, S6, S7, S8, S9, S10, S11, S12, COD)
VALUES
('$_POST[Date]', '$_POST[TechNum]','$_POST[Job]','$_POST[Account]','$_POST[Address]','$_POST[Equipment]','$_POST[First]','$_POST[S1]','$_POST[S2]','$_POST[S3]','$_POST[S4]','$_POST[S5]','$_POST[S6]','$_POST[S7]','$_POST[S8]','$_POST[S9]','$_POST[S10]','$_POST[S11]','$_POST[S12]','$_POST[COD]')";


if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "1 record added";
echo "<br />";

echo First;
echo "<br />";


$result = mysql_query("SELECT * FROM 'Pay Codes'
WHERE 'Code' = 'First'");

echo $result;
while($row = mysql_fetch_array($result))
{
echo $row['Code'] . " " . $row['Description'] . " " . $row['Price'];
echo "<br />";
}
echo $row;



mysql_close($con)
?>


When the routine runs after clicking the "Submit" button, MySQL responds with the following: "Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /Applications/XAMPP/xamppfiles/htdocs/insert.php on line 30" Line 30 is the "while" statement

Would somebody translate all this into English?

[MegaChriz]

$result = mysql_query("SELECT * FROM 'Pay Codes'
WHERE 'Code' = 'First'");

You should not use quotes on table and column names. You can use backticks (`) instead, but this is not required if you do not use variables in your query which has the same name as a table or column name used in your query. The backticks tell MySQL 'this is about a database, table or column name, not a variable'.

You should better do not use spaces in table/column names, but use underscores instead:

Code: Select all

_

Because you use a space in your table name, the table name will need backticks (or else MySQL thinks 'Pay' is the table name). This query should work:

Code: Select all

$result = mysql_query("SELECT * FROM `Pay Codes` WHERE Code = 'First'");

[timstring]

Your query returned a result. However, instead of returning the value of the variable 'First", it returned the string "First" and "Resource id #3". First, S1, S2, S3, . . . S12 are variables in the entry form that my php is processing into the table named "Worksheet" I want the script to match Codes in the table `Pay Codes` with the values that were entered on the form and return the matching Description and Price. In plain language, If the user enters "AC7" in the First field of the table "Worksheet", I want the routine to look up "AC7" in the "Codes" field of `Pay Codes` and return the description and price of the line corresponding to the Code "AC7".

If I leave off the quotes from around 'First' in your query to indicate that First is a variable, MySQL replies, "Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /Applications/XAMPP/xamppfiles/htdocs/insert.php on line 30"

[MegaChriz]

Okay, you want to lookup the code, description and price from the table 'Pay codes' where the code is the code posted in the form. Well, in that case you would need to use the value of $_POST['First'] in your query (if I have understanded you well). To do this, you will need to 'break-up' your query and put the variable $_POST['First'] in. To make sure user inputted data won't break the query you will need to wrap addslashes() around the $_POST variable. The PHP-function addslashes() will escape possible filled in quotes: http://www.php.net/addslashes

Your query would then look like this:

Code: Select all

$result = mysql_query("SELECT * FROM `Pay Codes` WHERE Code = '" . addslashes($_POST['First']) . "'");

Variables in MySQL
The variables I talked about earlier, were about variables in MySQL (not PHP). In MySQL you can alias tables and columns. This can be handy if you select data from multiple tables at once and/or if you have long table names. It can also be handy if you use MySQL functions to select data (such as COUNT()). An table or column can be aliased if you put the alias right after the table/column name. Optionally you can put 'AS' between the alias and the table/column name so you can easier see that you have defined an alias there, but this is not required for MySQL. An example:

Code: Select all

SELECT `Column_1` AS col1 FROM mytable

If you execute this query the column name in the result will be 'col1' instead of 'Column_1'.

An example when using COUNT(), this mysql function is used to count the number of rows:

Code: Select all

SELECT COUNT(*) AS numberofrows FROM mytable

This will return how many rows the table 'mytable' has, and the column name will be 'numberofrows' instead of 'COUNT(*)'.
This was beyond your question, but I thought you will understand better this way what I meant in my previous post.

[timstring]

You, sir, are a genius. I tip my hat to you.

tanksalot
tim

[timstring]

So, once the query returns "Price", how do I convert "Price" to a numeric variable so that as the script processes all 13 code fields, it totals the prices? something like: $JobPrice = $JobPrice + dowhatwith(Price) ?????

[MegaChriz]

I assume that 'Price' holds a numeric value in the database? To be sure, make sure the datatype for 'Price' you use in the database is 'DECIMAL' (set length for example to '10,2', this way you have 2 characters after the dot and 10 characters in total). If there are other characters in the column 'Price' (e.g. a dollar sign), you won't be able to calculate with this data.

Next you should make a PHP variable which will hold the total. Every time you will load the next row out of the database you can increase this variable. In the end you will have your total. Code:

Code: Select all

// Define variable $total, initiate it with zero
$total = 0;

// Load all rows from the query result
while($row = mysql_fetch_array($result))
{
  // Increase total with price
  $total += $row['Price'];

  echo $row['Code'] . " " . $row['Description'] . " " . $row['Price'];
  echo "<br />";
}
echo 'Total: ' . $total;