Pre-define variables in SQL strings

Hi All

Bit of newbie on some PHP things, although straight programming in PHP is
no problem with help of the net.

Currently working on a webinterface connecting to an existing MS Access Database. Connection to the database is up and data can be retreived.
So far so good.

What I would like to do is create a separate PHP file with the SQL strings
that are needed to retreive the information from the database. Example:

filename: sqlstrings.php

<?php
$billShow = 'SELECT Articles.*, [Bill Data].*, Bills.*, Customers.*
FROM Customers
INNER JOIN (Bills
INNER JOIN (Articles
INNER JOIN [Bill Data]
ON Articles.ArticleNumber = [Bill Data].ArticleNumber)
ON Bills.BillNumber = [Bill Data].BillNumber)
ON Customers.CustNumber = Bills.CustNumber
WHERE Bills.BillNumber = 704700
ORDER BY [Bill data].PrintSort';
?>

Please note that in this string the WHERE contains a fixed number. I would
like to have a variable here e.i. $myBillNr. so that later on in the use of the web interface I can use something like this

filename: webinterface.php

<?php

include 'sqlstrings.php';

$myBillNr = 704700;

$this->myDB->Query($billShow);

?>

To keep it simple left out all things that's not to the point. Simply I want
to declare the standard SQL strings outside the webpages, then set the
variable, and query the database, where of course the $billShow should
contain the value of the $myBillNr.

Any ideas if this is possible, and if so... how ?

Hints, help and links to some examples are highly appreciated.

Thanks

 

Have you tried using define() ?

<?php

include_once( 'myvariables.php' ); 

$billShow = 'SELECT Articles.*, [Bill Data].*, Bills.*, Customers.*
FROM Customers
INNER JOIN (Bills
INNER JOIN (Articles
INNER JOIN [Bill Data]
ON Articles.ArticleNumber = [Bill Data].ArticleNumber)
ON Bills.BillNumber = [Bill Data].BillNumber)
ON Customers.CustNumber = Bills.CustNumber
WHERE Bills.BillNumber = ' . BILLNUMBER . '
ORDER BY [Bill data].PrintSort';
?>

PHP:

Then in the myvariables.php file you would have something like this:

<?php

define( 'BILLNUMBER',  '704700' );

?>

PHP:

Brew

 

Hi Brew,

Thanks for the answer. Definitely you're right on this, but that's because
I forgot to mention that in the end I want the bill number to come from a
selection box on the website, so the number is not constant, but dynamical
depending on the input of the user.

I could do it with a str_replace, but would prefer to have a variable that
is automatically set to the correct value after a submit.

Bart

 

It sounds like you want to get the value from a form. If you look at this page: http://www.w3schools.com/html/html_forms.asp

When a form is submitted you will be able to retreive the data in the $_POST array.

Brew

 

Hi Brew,

Yes indeed... $_POST['var'] lets say is the chosen bill number.

in the action file of the form, I get the $_POST['var'] and want
to do a functioncall to for example

$this->myDB->Query($billShow);

The predefind $billShow must get the value of the $_POST['var']
for the bill number, as such declaration of the $billShow would
look like

$billShow = 'SELECT Articles.*, [Bill Data].*, Bills.*, Customers.*
FROM Customers
INNER JOIN (Bills
INNER JOIN (Articles
INNER JOIN [Bill Data]
ON Articles.ArticleNumber = [Bill Data].ArticleNumber)
ON Bills.BillNumber = [Bill Data].BillNumber)
ON Customers.CustNumber = Bills.CustNumber
WHERE Bills.BillNumber = $_POST['var']
ORDER BY [Bill data].PrintSort';

using a own defined variable in the string always gives an error.
However, now I will take a try if it can work using the $_POST['var']
directly as it is a global in PHP....

Thanks for giving me the hint on $_POST ... your advise was not
what I meant but it give me something worth to try.

 

Try this:

$billShow = 'SELECT Articles.*, [Bill Data].*, Bills.*, Customers.*
FROM Customers
INNER JOIN (Bills
INNER JOIN (Articles
INNER JOIN [Bill Data]
ON Articles.ArticleNumber = [Bill Data].ArticleNumber)
ON Bills.BillNumber = [Bill Data].BillNumber)
ON Customers.CustNumber = Bills.CustNumber
WHERE Bills.BillNumber = ' . (int)$_POST['var'] . '
ORDER BY [Bill data].PrintSort';

PHP:

Is there a function in database library for sanitising the $_POST['var'] variable ? As this is just a number I have made sure that only a number is passed to the query, but you should read up on sql injection to make sure that potential threats are eliminated.

Brew

 

Hi Brew,

Thanks for the advice. The bill numbers will be extracted from the database
by a PHP function. Thus selection normally will be one existing number only.
Further more the webinterface will be used by one person only and will require
a login (login scripts I did before with PHP).

The database is some kind of mini-ERP that I built for a friend of mine who has
a own 1-man business. As he's on the road a lot, an internet interface is very useful for him.

Reason I want to abstract the SQL, is that the example is only one of the many quite complex SQL statements, and I want to keep the PHP code clear readable.

I know I should be carefull in case the combo box is hijacked and other SQL instructions are injected with the bill number....

Thanks for the help so far