Page 1 of 3 123 LastLast
Results 1 to 15 of 42

Thread: Query result column separation...

  1. #1
    Registered
    Join Date
    Aug 2003
    Location
    Columbus, Ohio
    Posts
    122

    Query result column separation...

    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

  2. #2
    Senior Member chromate's Avatar
    Join Date
    Aug 2003
    Location
    UK
    Posts
    2,348
    yep. try this:


    PHP Code:
    <?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.

  3. #3
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,055
    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.
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

  4. #4
    Registered
    Join Date
    Aug 2003
    Location
    Columbus, Ohio
    Posts
    122
    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

  5. #5
    Registered
    Join Date
    Aug 2003
    Location
    Columbus, Ohio
    Posts
    122
    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

  6. #6
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,055
    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.
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

  7. #7
    Registered
    Join Date
    Aug 2003
    Location
    Columbus, Ohio
    Posts
    122
    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

  8. #8
    Registered
    Join Date
    Aug 2003
    Location
    Columbus, Ohio
    Posts
    122
    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

  9. #9
    Registered
    Join Date
    Aug 2003
    Location
    Columbus, Ohio
    Posts
    122
    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?

  10. #10
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,055
    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 "="
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

  11. #11
    Registered
    Join Date
    Aug 2003
    Location
    Columbus, Ohio
    Posts
    122
    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!

  12. #12
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,055
    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:

    Code:
    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'];
    
    }
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

  13. #13
    Registered
    Join Date
    Aug 2003
    Location
    Columbus, Ohio
    Posts
    122
    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...

  14. #14
    Registered
    Join Date
    Aug 2003
    Location
    Columbus, Ohio
    Posts
    122
    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?

  15. #15
    Administrator Chris's Avatar
    Join Date
    Feb 2003
    Location
    East Lansing, MI USA
    Posts
    7,055
    The if submit thing takes the place of isset.

    try this

    $line[barcode]; instead of $line['barcode'] I never use single quotes.
    Chris Beasley - My Guide to Building a Successful Website[size=1]
    Content Sites: ABCDFGHIJKLMNOP|Forums: ABCD EF|Ecommerce: Swords Knives

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •