Building an array into a query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Building an array into a query

Post by rsmarsha »

I'll explain the context first. :)

I have a product database containing all the product info.

A form passing variables through in the form $postName = category, $postValue = product id.

I then need to enter the description from products into a table containing the order info such as the descriptions and other info. This means i need to loop through the id's to get all the descriptions, which i am then putting into an array. This array the needs to form part of a query.

Code:

Code: Select all

//this builds the array containing the product descriptions
 foreach ($_POST as $postName => $postValue) { 
if (($postName>0 && $postName<31) && $postName!='quantity') {
$display = "SELECT * FROM categories,products WHERE categories.cat_id=products.cat_id AND categories.cat_id='$postName' AND prod_id='$postValue'";
$dq = mysql_query($display) or die("Query $display Failed".mysql_error());
$array=array(); 
$i=0; 
while ($type_row=mysql_fetch_object($dq)) 
{ 
$array[$i]=',\''.$type_row->desc.'\'';
$i++; 
} 
	}

		}

//query

$enterorder = "INSERT INTO orders (orderno,username,dateadded,status,archive,price,exvat,payment,cpu,memory,motherboard,usb,hdd,hdda,raid,cdrom,dvd,dvdw,gcard,gcarda,sound,modem,network,floppy,cardreader,pccase,psu,osreq,firewire,monitor,keyboard,mouse,speakers,printer,virus,office,tvcard,onsite,q,discount,total,totalexvat,deldet,refer,bat,ccase,pbar,l_model,config) VALUES ('','$mail','$datejoined','$type','','$_POST[price]','$_POST[exvat2]','Finance'
//array would go in this bit of the query
,'$_POST[quantity]','$_POST[discount]','$_POST[final]','$_POST[extotal]','$_POST[deldet]','$refer'$array2)";
The reason it's done like this is the order table was already there, originally using a text file for the info, i'm putting the products in the database and creating a new ordering process.

All the post variables such as the prod cats and id's, price and other bits are passed through from an earlier page. As the order table contains product descriptions and price i've had to use the above loop to create the array, missing out some of the posted variables.

Any advice is greatly appreciated. :)
User avatar
BDKR
DevNet Resident
Posts: 1207
Joined: Sat Jun 08, 2002 1:24 pm
Location: Florida
Contact:

Post by BDKR »

I wouldn't call your code legible. :wink:

Code: Select all

//this builds the array containing the product descriptions
foreach ($_POST as $postName => $postValue)
    {
    if (($postName>0 && $postName<31) && $postName!='quantity')
        {
        $display = "SELECT * FROM categories,products WHERE categories.cat_id=products.cat_id AND
        categories.cat_id='$postName' AND prod_id='$postValue'";
        $dq = mysql_query($display) or die("Query $display Failed".mysql_error());
        $array=array();
        $i=0;
        while ($type_row=mysql_fetch_object($dq))
            {
            $array[$i]=',\''.$type_row->desc.'\'';
            $i++;
            
            /* Put your insert query here! Also run that insert query here as well. */
            }
        }
    }
As for updating the query at each loop, php has a ton of string manipulation functions that can get you over the hump here.

Cheers
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

I'd probably use insert select
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post by rsmarsha »

I've used insert select before, but i need to insert the post variables as one query.

eg.

orders table

orderno prod1 prod 2 and so on.

:)

Sorry about the code, hehe didn't look like that before i'm sure. :)
Last edited by rsmarsha on Thu Jan 12, 2006 3:00 am, edited 1 time in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Meaby i'm not awake well, but the following would work too:

Code: Select all

$mysql['somepostedvariable'] = mysql_real_escape_string($_POST['something']);

INSERT INTO sometable 
SELECT somevalue, othervalue, $mysql['somepostedvariable'], yetanothervalue
FROM othertable
rsmarsha
Forum Contributor
Posts: 242
Joined: Tue Feb 08, 2005 4:06 am
Location: Leeds, England

Post by rsmarsha »

I'd have to do that for each of the 30 variables though, wouldn't I?

First for each posted variable i have to pull out a description from the product table and then insert all those into one row of another table.
Post Reply