Query result column separation...

[Stevens]

This might be a stupid question, but I need to separate each column in a returned query result to be defined as its own variable for automatic form field population. The only way I have come up with to get this accomplished is to run a separate query for each column I want returned. Is there a way to just return the whole row in one query result then to separate the fields into their variables from there? That would be a lot shorter!!! thanx for any help...

E

[chromate]

yep. try this:




<?php

$sql = "SELECT * FROM table_name";
$result = mysql_query($sql, $db);

while( $rw = mysql_fetch_array($result) ) {
echo $rw['col_name_one'];
echo $rw['col_name_two'];
}

?>


Basically just use * instead of selecting the individual column name.

Hope this helps.

[Chris]

Um....

Why don't you post some of your code (be sure to edit out passwords/usernames).

If I understand your question right this is a very simple thing that you're just over looking.

[Stevens]

This is my conditional. Keep in mind that the form that is at the bottow will submit its info to itself which is why the first if () is set:

if (isset($_POST['barcode'])) {
$bc = $_POST['barcode'];
$sn = $_POST['serial_number'];
$loc = $_POST['location'];
$man = $_POST['manufacturer'];
$mod = $_POST['model'];
$item = $_POST['item'];
$it = $_POST['item_type'];
$pr = $_POST['price'];
$war = $_POST['warranty'];
$we = $_POST['warranty_expiration'];
$cn = $_POST['contract_number'];
$pn = $_POST['part_number'];
$dr = $_POST['date_received'];
$ex = $_POST['excess'];
$ed = $_POST['excess_date'];
$xha = $_POST['xtra_hardware_a'];
$xhb = $_POST['xtra_hardware_b'];
$xhc = $_POST['xtra_hardware_c'];

$query = "UPDATE invtemp SET serial_number = $sn WHERE barcode = $bc;
UPDATE invtemp SET location = $loc WHERE barcode = $bc;
UPDATE invtemp SET manufacturer = $man WHERE barcode = $bc;
UPDATE invtemp SET model = $mod WHERE barcode = $bc;
UPDATE invtemp SET item = $item WHERE barcode = $bc;
UPDATE invtemp SET item_type = $it WHERE barcode = $bc;
UPDATE invtemp SET price = $pr WHERE barcode = $bc;
UPDATE invtemp SET warranty = $war WHERE barcode = $bc;
UPDATE invtemp SET warranty_expiration = $we WHERE barcode = $bc;
UPDATE invtemp SET contract_number = $cn WHERE barcode = $bc;
UPDATE invtemp SET part_number = $pn WHERE barcode = $bc;
UPDATE invtemp SET date_received = $dr WHERE barcode = $bc;
UPDATE invtemp SET ecxcess = $ex WHERE barcode = $bc;
UPDATE invtemp SET excess_date = $ed WHERE barcode = $bc;
UPDATE invtemp SET xtra_hardware_a = $xha WHERE barcode = $bc;
UPDATE invtemp SET xtra_hardware_b = $xhb WHERE barcode = $bc;
UPDATE invtemp SET xtra_hardware_c = $xhc WHERE barcode = $bc;";

mssql_query ($query);
} else {
$query = "SELECT barcode, serial_number, location, manufacturer, model, item, item_type, price, warranty, warranty_expiration, contract_number, part_number, date_received, excess, excess_date, xtra_hardware_a, xtra_hardware_b, xtra_hardware_c FROM invtemp WHERE barcode LIKE '%$bc%';"; //specify columns to be selected

$result = mssql_query ($query); //run the query

while($line = mssql_fetch_row($result));

$bc = $line['barcode'];
$sn = $line['serial_number'];
$loc = $line['location'];
$man = $line['manufacturer'];
$mod = $line['model'];
$item = $line['item'];
$it = $line['item_type'];
$pr = $line['price'];
$war = $line['warranty'];
$we = $line['warranty_expiration'];
$cn = $line['contract_number'];
$pn = $line['part_number'];
$dr = $line['date_received'];
$ex = $line['excess'];
$ed = $line['excess_date'];
$xha = $line['xtra_hardware_a'];
$xhb = $line['xtra_hardware_b'];
$xhc = $line['xtra_hardware_c'];

}

?>

// I then have a form that I will need to have populated with the info from this one row to make it editable for the users.

tr>
<td height="689"><form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"><fieldset>
<p align="right"><font face="Verdana, Arial, Helvetica, sans-serif"><strong>*Barcode:
<input type="text" name="barcode" value="<?php echo $bc; ?>" />
</strong></font></p>

<p align="right"><strong><font face="Verdana, Arial, Helvetica, sans-serif">*Serial
Number:</font></strong>
<input type="text" name="serial_number" value="<?php echo $sn; ?>" />
</p>

//These are examples of my first two text fields...you get my drift. The bugger is that I get no PHP errors, but my form fields to not get populated with any information to be edited. Any suggestions?

E

[Stevens]

This is my conditional. Keep in mind that the form that is at the bottow will submit its info to itself which is why the first if () is set:

if (isset($_POST['barcode'])) {
$bc = $_POST['barcode'];
$sn = $_POST['serial_number'];
$loc = $_POST['location'];
$man = $_POST['manufacturer'];
$mod = $_POST['model'];
$item = $_POST['item'];
$it = $_POST['item_type'];
$pr = $_POST['price'];
$war = $_POST['warranty'];
$we = $_POST['warranty_expiration'];
$cn = $_POST['contract_number'];
$pn = $_POST['part_number'];
$dr = $_POST['date_received'];
$ex = $_POST['excess'];
$ed = $_POST['excess_date'];
$xha = $_POST['xtra_hardware_a'];
$xhb = $_POST['xtra_hardware_b'];
$xhc = $_POST['xtra_hardware_c'];

$query = "UPDATE invtemp SET serial_number = $sn WHERE barcode = $bc;
UPDATE invtemp SET location = $loc WHERE barcode = $bc;
UPDATE invtemp SET manufacturer = $man WHERE barcode = $bc;
UPDATE invtemp SET model = $mod WHERE barcode = $bc;
UPDATE invtemp SET item = $item WHERE barcode = $bc;
UPDATE invtemp SET item_type = $it WHERE barcode = $bc;
UPDATE invtemp SET price = $pr WHERE barcode = $bc;
UPDATE invtemp SET warranty = $war WHERE barcode = $bc;
UPDATE invtemp SET warranty_expiration = $we WHERE barcode = $bc;
UPDATE invtemp SET contract_number = $cn WHERE barcode = $bc;
UPDATE invtemp SET part_number = $pn WHERE barcode = $bc;
UPDATE invtemp SET date_received = $dr WHERE barcode = $bc;
UPDATE invtemp SET ecxcess = $ex WHERE barcode = $bc;
UPDATE invtemp SET excess_date = $ed WHERE barcode = $bc;
UPDATE invtemp SET xtra_hardware_a = $xha WHERE barcode = $bc;
UPDATE invtemp SET xtra_hardware_b = $xhb WHERE barcode = $bc;
UPDATE invtemp SET xtra_hardware_c = $xhc WHERE barcode = $bc;";

mssql_query ($query);
} else {
$query = "SELECT barcode, serial_number, location, manufacturer, model, item, item_type, price, warranty, warranty_expiration, contract_number, part_number, date_received, excess, excess_date, xtra_hardware_a, xtra_hardware_b, xtra_hardware_c FROM invtemp WHERE barcode LIKE '%$bc%';"; //specify columns to be selected

$result = mssql_query ($query); //run the query

while($line = mssql_fetch_row($result));

$bc = $line['barcode'];
$sn = $line['serial_number'];
$loc = $line['location'];
$man = $line['manufacturer'];
$mod = $line['model'];
$item = $line['item'];
$it = $line['item_type'];
$pr = $line['price'];
$war = $line['warranty'];
$we = $line['warranty_expiration'];
$cn = $line['contract_number'];
$pn = $line['part_number'];
$dr = $line['date_received'];
$ex = $line['excess'];
$ed = $line['excess_date'];
$xha = $line['xtra_hardware_a'];
$xhb = $line['xtra_hardware_b'];
$xhc = $line['xtra_hardware_c'];

}

?>

// I then have a form that I will need to have populated with the info from this one row to make it editable for the users.

tr>
<td height="689"><form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post"><fieldset>
<p align="right"><font face="Verdana, Arial, Helvetica, sans-serif"><strong>*Barcode:
<input type="text" name="barcode" value="<?php echo $bc; ?>" />
</strong></font></p>

<p align="right"><strong><font face="Verdana, Arial, Helvetica, sans-serif">*Serial
Number:</font></strong>
<input type="text" name="serial_number" value="<?php echo $sn; ?>" />
</p>

//These are examples of my first two text fields...you get my drift. The bugger is that I get no PHP errors, but my form fields to not get populated with any information to be edited. Any suggestions?

E

[Chris]

Couple things.

1. Only use the LIKE qualifier in a WHERE statement if you need to do a text comparison.

For instance in a search program, WHERE title LIKE '%Ring%', would return "Lord of the Rings", "The Ring", etc.

If you want something exact, like record 8452, simply use "="

WHERE number = "8452" or WHERE title = "The Ring"

So if you know exactly what you want, use "=" not "Like"

The other issue is that you only need one UPDATE query.

UPDATE table tablename set var1 = '$var1', var2 = '$var2....

You don't need a query for each individual variable.

Now, as for your problem, I'm guessing the query isn't working so all the variables are blank.

Try inserting this:

$num = mssql_num_rows($result);
echo $num;

If you get "0" then the problem is your query isn't returning any results.

[Stevens]

I tried to use the = qualifier, but I received an error saying that with the data type specified I could not use it. Do you know what data types I can use the = qualifier rather than LIKE?

E

[Stevens]

Do you think I'm using the if (isset()) function correctly? What I need to happen is if the page comes up and is handling its own form data to process the info. If the code runs from the /edit.php?id=barcode link, the ELSE part of the conditional needs to be executed. Errors I have received seem to denote that the former part of the conditional is being parsed when the second part should be getting handled. As you can see I have two sets of identical variables as not to confuse the form fields making this a pretty important thing...any suggestions?

E

[Stevens]

I used the mssql_num_rows() function and returned 1, which is correct, but it looks as if the variables are getting assigned improperly. Do you think I should make the form code part of the php script instead of having smaller scripts outside the main one? Are assigned variables common from script to script?

[Chris]

Originally posted by Stevens
I tried to use the = qualifier, but I received an error saying that with the data type specified I could not use it. Do you know what data types I can use the = qualifier rather than LIKE?

E

You probably have the barcode datatype set wrong.

LIKE is only supposed to be used with blob type fields, which in general are large sets of text or binary data (pictures).

Anything else should work with an "="

[Stevens]

I tried to do the consolidated script thing to no avail. I also tried to echo $bc; after the second set of variable definitions but no value was returned...ugh!

[Chris]

What I do in situations like this is assign a value to the submit button and then see if it equals.

if($submit == "Submit"){
process data
}

I do see one problem I didn't notice before:



while($line = mssql_fetch_row($result)); (should be {)

$bc = $line['barcode'];
$sn = $line['serial_number'];
$loc = $line['location'];
$man = $line['manufacturer'];
$mod = $line['model'];
$item = $line['item'];
$it = $line['item_type'];
$pr = $line['price'];
$war = $line['warranty'];
$we = $line['warranty_expiration'];
$cn = $line['contract_number'];
$pn = $line['part_number'];
$dr = $line['date_received'];
$ex = $line['excess'];
$ed = $line['excess_date'];
$xha = $line['xtra_hardware_a'];
$xhb = $line['xtra_hardware_b'];
$xhc = $line['xtra_hardware_c'];

}

[Stevens]

while ($line = mssql_fetch_row($result)) {

$bc = $line['barcode'];
$sn = $line['serial_number'];
$loc = $line['location'];
$man = $line['manufacturer'];
$mod = $line['model'];
$item = $line['item'];
$it = $line['item_type'];
$pr = $line['price'];
$war = $line['warranty'];
$we = $line['warranty_expiration'];
$cn = $line['contract_number'];
$pn = $line['part_number'];
$dr = $line['date_received'];
$ex = $line['excess'];
$ed = $line['excess_date'];
$xha = $line['xtra_hardware_a'];
$xhb = $line['xtra_hardware_b'];
$xhc = $line['xtra_hardware_c'];
}

Is that right? I put that in and got "Notice: Undefined index: barcode in ..." errors for every variable...

[Stevens]

Also, where should I insert the if($submit == "Submit"){
process data
} function? Can I cut some stuff out then or even take away the conditional?

[Chris]

The if submit thing takes the place of isset.

try this

$line[barcode]; instead of $line['barcode'] I never use single quotes.

[Stevens]

I have my WHILE conditional inside the ELSE conditional...is that legit?

[Chris]

It is, but you need to remember that loops need brackets too. You're missing brackets around your loop.

[Stevens]

I figured it out. The mssql_fetch_row() function returns only an enumerated array, not an associative array like the mssql_fetch_array() function does. In my WHILE loop I defined variables as $line['barcode'] (associatively) for example rather than enumerated...i.e. $line[0]. I needed to either change the function to mssql_fetch_array() and specify the MSSQL_ASSOC parameter (which is not valid with the mssql_fetch_row() function) of reassign the variables to reflect their enumerated values. I chose the enumerated value designations and now the form fields are populating as they should...

example:
while ($line = mssql_fetch_row($result)) {
$bc = $line[0];
$sn = $line[1];
$loc = $line[2];
$man = $line[3];
$mod = $line[4];
$item = $line[5];
$it = $line[6];
$pr = $line[7];
$war = $line[8];
$we = $line[9];
$cn = $line[10];
$pn = $line[11];
$dr = $line[12];
$ex = $line[13];
$ed = $line[14];
$xha = $line[15];
$xhb = $line[16];
$xhc = $line[17];

}

e

[Stevens]

Hey Chris, if you still feel like looking at this...

Everything is gooing alright now, but I can't get the update query to run correctly. The process is as follows:

1. The page loads with a pre-specified variable edit.php?id=<barcode>
2. The ELSE portion of the conditional executes and populates the form fields with the current information.
3. The user changes informatin about the object then hits the "Submit" button.
4. The edit.php page processes itself, but this time the IF portion of the conditional executes because $submit holds a value.
5. The fields submitted are assigned variables identical to the variables in the ELSE statement so the same form processes each part of the conditional identically.
6. The UPDATE query is run updating the MSSQL fields.
7. The form populates itself with the new user-specified information.

OK, the form populates itself with the proper updated informatin after the "Submit" button is hit so I know the variables it the IF segment are getting assigned correctly. The problem is that the UPDATE query is not updating the actual db fields. I am not getting a "failed query" error either so I'm a bit lost. Do you think I should run the UPDATE query using the $_POST variables then assign form-friendly variables afterwards? Little help...well, lots of help!

E

[Stevens]

I stuck the mssql_num_rows() function that you gave me earler after the query execution in the IF statement.

$num = mssql_num_rows($result);
echo $num;

I returned the following error:

Warning: mssql_num_rows(): supplied argument is not a valid MS SQL-result resource in ....

Do you think I have the mssql_query() syntax correct? Maybe there is a problem with the query being a query that UPDATEs rather than GETs...???

[chromate]

try:



UPDATE invtemp SET location = '$loc', barcode='$bc' WHERE barcode = '$bc';


But for all of the columns. Notice I've added quotes around the variables. I haven't been following what's been happening, so I may be wrong.

[Stevens]

Dang! I thought that was going to be it...no go though. Here is the entire if () statement...

if (isset ($submit)){
$bc = $_POST['barcode'];
$sn = $_POST['serial_number'];
$loc = $_POST['location'];
$man = $_POST['manufacturer'];
$mod = $_POST['model'];
$item = $_POST['item'];
$it = $_POST['item_type'];
$pr = $_POST['price'];
$war = $_POST['warranty'];
$we = $_POST['warranty_expiration'];
$cn = $_POST['contract_number'];
$pn = $_POST['part_number'];
$dr = $_POST['date_received'];
$ex = $_POST['excess'];
$ed = $_POST['excess_date'];
$xha = $_POST['xtra_hardware_a'];
$xhb = $_POST['xtra_hardware_b'];
$xhc = $_POST['xtra_hardware_c'];

$query = "UPDATE invtemp SET serial_number = '$sn', location = '$loc', manufacturer = '$man', model = '$mod', item = '$item', item_type = '$it', price = '$pr', warranty = '$war', warranty_expiration = '$we', contract_number = '$cn', part_number = '$pn', date_received = '$dr', ecxcess = '$ex', excess_date = '$ed', xtra_hardware_a = '$xha', xtra_hardware_b = '$xhb', xtra_hardware_c = '$xhc' WHERE barcode = '$bc';";

$result = @mssql_query ($query);

}

[Stevens]

Oh by the way, Chromate...you answered my original question way back in the beginning of this thread...so thanx...

This is the third roadblock that I have run into. I kept the same thread alive as all the issues are related. It's nice ot have another few pairs of eyes. Mine keep seeing things the same way, you know?

E

[Chris]

remove the "@" from the front of the mssql_query function. I believe that is supressing an error message.

[chromate]

right after where you set the value of $query do an echo $query to see exactly what the sql statement looks like.

Do you have direct access to the database or through phpMyAdmin or the like? If so, run the query from the command line and see what error it comes up with.

Also get rid of the @ infront of mysql_query($query). This way it wont suppress the errors.

[chromate]

actually, cancel that bit about phpMyAdmin. Didn't realise you were using MS SQL Server.

[chromate]

I've seen the error...

You've put ecxcess='$ex' instead of excess='$ex' :)

[Stevens]

You know you all are daggone geniouses...

I took out the @ and did return an error.

For some reason the data type of the "price" column wasn't working well. It had a data type of MONEY (MS Access), but was somehow getting updated with a char data type. I had to go into the db (which was upsized through MS's automatic data coversion tool from Access to MSSQL2K) and change the money type to char. Now everything works great! Thanks everyone for your input...I'm sure there will be more...lol...

E

[Chris]

Some database systems do not like quotes around variables of certain data types.

Generally numerical or date datatypes are like this.

If you were using MySQL it wouldn't be an issue, but MS products are a little different.

[chromate]

Actually, it can even cause problems in MySQL putting everything in quotes. It's not ideal having money as a char really.

[Stevens]

I know...I stick to char, varchar, and text, but unfortunately I didn't have the pleasure of creating the original db. It's kind of an inheritance. My predecessor had it in Access and updated it via a data source and a hand scanner. I said to heck with that...we're going web! I do need to get an applicatin together in vb or something that will allow me to scan then to sync to our server...but all in the future...thanx again...

E

[Chris]

MySQL is fine with it, trust me.

[chromate]

Yeh, but the fact it's fine with it *is* the problem ;) MySQL is terrible when it comes to data integrity (and referential). It's therefore down to the programmer to treat it with care.

[Stevens]

That's a good thing right? Aren't the programmers supposed to treat it with care anyway? I think the fact that every detail needs attention leaves out a lot of the guess-work...

[chromate]

We're human, we make mistakes. We should be careful, but when it comes to databases their integrity is paramount. It's not so important on smaller databases, but when they get large it's very important and it can be extremely costly to put things right.

Enterprise level DBMS's, such as Oracle for example, will enforce data integrity every-which-way, including datatypes. They in fact reduce the "guess work" by ensuring the database is being used consistently.

[Stevens]

Good point...makes sense tro me. I had tons of issues when coverting the data the first time which was a pain in the butt, but I guess that it's all working the way it should now that all data is consistent. Making the db from scratch sure would have helped. There are just a lot of variables that need to be consistent...so to speak...

E

[Chris]

I like how MySQL lets you quote everything -- it protects you from sql injection attacks.

[chromate]

Injection attacks?

[Chris]

It deals with tainted data.

Someone could figure out how to pass an SQL statement as part of a variable to your script and they could use that statement to muck up your database.

[chromate]

How do quotes guard against that? If anything I would have thought it would make it more vulnerable?

[Chris]

INSERT INTO table set var1 = 897897; delete from main

Where the variable is "897897; delete from main'

Or...

INSERT INTO table set var = '897897; delete from main'

the other way to guard against this is to give your webpages only the type of access they need (read/write/dba).

[chromate]

Interesting point, but if you're inserting numeric data you would need to validate it on a different layer to make sure it's within bounds etc anyway. With text / varchar etc you would use quotes anyway, so there wouldn't be any risk.