SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2011
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Php + Mysql Query Results used to create new query

    Hi all,

    My Goal is simple, I have one table where multiple users submit forms and update the table with their information. What I am looking for is to bring up previous entries to come up alongside the current entry that has been recently put in.

    So I have

    PHP Code:
    <?php

     mysql_connect
    ("*****","*****","*******") or die(mysql_error()); 
     
    mysql_select_db("******") or die(mysql_error()); 
     
     
    $sql2 "Select * From formdata WHERE rc ='' AND mf='' AND pf=''";
    $result2 MYSQL_QUERY($sql2);
    while(
    $row2=mysql_fetch_array($result2))


    if(
    $row2[priors] == 'yes') {
        
    $sql3 "Select * From formdata WHERE loc == $row2[loc] and lastname == $row2[lastname] and firstname == $row2[firstname]";
    $result3 MYSQL_QUERY($sql3);
    $row3=mysql_fetch_array($result3);
        
    if (
    $row2[statread] == 'yes'){echo "
    <tr><td bgcolor='red'><center><input type='checkbox' name='completed[]' value='
    $row2[form_id]'></center></td>
    <td bgcolor='red'><center><input type='checkbox' name='strans[]' value='
    $row2[form_id]'></center></td>
    <td bgcolor='red'>
    $row2[loc]</td>
    <td bgcolor='red'>
    $row2[doe]</td>
    <td bgcolor='red'>
    $row2[lastname]</td>
    <td bgcolor='red'>
    $row2[firstname]</td>
    <td bgcolor='red'>
    $row2[dob]</td>
    <td bgcolor='red'>
    $row2[et]</td>
    <td bgcolor='red'>
    $row2[v]</td>
    <td bgcolor='red'><a href='#' onclick='showhide('div
    $row2[form_id]');('first time click');{return false;}; return false;'>$row2[priors]</td>

        <div id='div
    $row2[form_id]' style='display: none;'>
        <tr><td></td>
        <td></td>
        <td>
    $row3[loc]</td>
        <td>
    $row3[doe]</td>
        <td>
    $row3[lastname]</td>
        <td>
    $row3[firstname]</td>
        <td>
    $row3[dob]</td>
        <td>
    $row3[et]</td>
        <td>
    $row3[v]</td>
        <td><a href='' onClick='document.frm1.submit();return false'><input type='checkbox' name='report[]' value='
    $row3[form_id]'>$row3[priors]</td>
        <td>
    $row3[history]</td>
        <td>
    $row3[notes]</td>
        <td>
    $row3[op]</td><
        td>
    $row3[contactphone]</td>
        <td>
    $row3[sr]</td>
        <td><a href='/req/uploads/
    $row3[filepath]' target='_blank'>$row3[filename]</a></td>
        </tr>
        </div>
        
    <td bgcolor='red'>
    $row2[history]</td>
    <td bgcolor='red'>
    $row2[notes]</td>
    <td bgcolor='red'>
    $row2[op]</td>
    <td bgcolor='red'>
    $row2[contactphone]</td>
    <td bgcolor='red'>
    $row2[sr]<td bgcolor='red'>$row2[filename]</td>
    <td bgcolor='red'><center><input type='checkbox' name='missing[]' value='
    $row2[form_id]'></td></tr>";} 


    else {echo 
    "<tr><td><center><input type='checkbox' name='completed[]' value='$row2[form_id]'></center></td>
    <td><center><input type='checkbox' name='strans[]' value='
    $row2[form_id]'></center></td>
    <td>
    $row2[loc]</td>
    <td>
    $row2[doe]</td>
    <td>
    $row2[lastname]</td>
    <td>
    $row2[firstname]</td>
    <td>
    $row2[dob]</td>
    <td>
    $row2[et]</td>
    <td>
    $row2[v]</td>
    <td><a href='#' onclick='showhide('div
    $row2[form_id]');('first time click');{return false;}; return false;'>$row2[priors]</td>
    <td>
    $row2[history]</td>
    <td>
    $row2[notes]</td>
    <td>
    $row2[op]</td>
    <td>
    $row2[contactphone]</td>
    <td>
    $row2[sr]<td>$row2[filename]</td>
    <td><center><input type='checkbox' name='missing[]' value='
    $row2[form_id]'></td></tr>

        <div id='div
    $row2[form_id]' style='display: none;'>
        <tr><tr><td></td>
        <td></td>
        <td>
    $row3[loc]</td>
        <td>
    $row3[doe]</td>
        <td>
    $row3[lastname]</td>
        <td>
    $row3[firstname]</td>
        <td>
    $row3[dob]</td>
        <td>
    $row3[et]</td>
        <td>
    $row3[views]</td>
        <td><a href='' onClick='document.frm1.submit();return false'><input type='checkbox' name='report[]' value='
    $row3[form_id]'>$row3[priors]</td>
        <td>
    $row3[history]</td>
        <td>
    $row3[notes]</td>
        <td>
    $row3[op]</td><
        td>
    $row3[contactphone]</td>
        <td>
    $row3[sr]</td>
        <td><a href='/req/uploads/
    $row3[filepath]' target='_blank'>$row3[filename]</a></td>
        </tr>
        </div>
    "
    ;}
    }

    else {
            
    if (
    $row2[statread] == 'yes'){echo <tr><td bgcolor='red'><center><input type='checkbox' name='completed[]' value='$row2[form_id]'></center></td>
    <
    td bgcolor='red'><center><input type='checkbox' name='strans[]' value='$row2[form_id]'></center></td>
    <
    td bgcolor='red'>$row2[loc]</td>
    <
    td bgcolor='red'>$row2[doe]</td>
    <
    td bgcolor='red'>$row2[lastname]</td>
    <
    td bgcolor='red'>$row2[firstname]</td>
    <
    td bgcolor='red'>$row2[dob]</td>
    <
    td bgcolor='red'>$row2[et]</td>
    <
    td bgcolor='red'>$row2[v]</td>
    <
    td bgcolor='red'>$row2[priors]</td>
    <
    td bgcolor='red'>$row2[history]</td>
    <
    td bgcolor='red'>$row2[notes]</td>
    <
    td bgcolor='red'>$row2[op]</td>
    <
    td bgcolor='red'>$row2[contactphone]</td>
    <
    td bgcolor='red'>$row2[sr]<td bgcolor='red'>$row2[filename]</td>
    <
    td bgcolor='red'><center><input type='checkbox' name='missing[]' value='$row2[form_id]'></td></tr>";} 
    "
    ;} 
    else {echo 
    "<tr><td bgcolor='red'><center><input type='checkbox' name='completed[]' value='$row2[form_id]'></center></td>
    <td><center><input type='checkbox' name='strans[]' value='
    $row2[form_id]'></center></td>
    <td>
    $row2[loc]</td>
    <td>
    $row2[doe]</td>
    <td>
    $row2[lastname]</td>
    <td>
    $row2[firstname]</td>
    <td>
    $row2[dob]</td>
    <td>
    $row2[et]</td>
    <td>
    $row2[v]</td>
    <td>
    $row2[priors]</td>
    <td>
    $row2[history]</td>
    <td>
    $row2[notes]</td>
    <td>
    $row2[op]</td>
    <td>
    $row2[contactphone]</td>
    <td>
    $row2[sr]<td bgcolor='red'>$row2[filename]</td>
    <td><center><input type='checkbox' name='missing[]' value='
    $row2[form_id]'></td></tr>";} 
    ";}
    }
    ?>

    It does not work because it does not provide the results of $sql3, I changed the code while ($row3=mysql_fetch_array($result3)) {

    but it then did not show $row2 results that fell in the category of having priors....

    Any Ideas? I understand that nesting queries is not always the best procedure but since I already have one table I figured it should be too difficult.

  2. #2
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First of all, you have many problems and even I found some syntax errors as well. Haven't you seen any fatal errors when you run the script? You have to check the strings quotes. Some are not started and some are closed without opening ". Please check them.

    Secondly, why have you used while here?
    PHP Code:
    while($row2=mysql_fetch_array($result2)) 
    This statement is wrong. If you expect multiple results from the query then you must use while with a block of code to be executed in the loop. Otherwise you don't have to use while if it is supposed to be one result:
    PHP Code:
    $row2=mysql_fetch_array($result2); 
    Check if the query has retrieved results/rows or not:
    PHP Code:
    if(mysql_num_rows($result2) >= 1){
        
    $row2=mysql_fetch_array($result2);
    }
    else{
        echo 
    "no results found";

    And another big problem is, no double equals to (==) are used in the database query. Also all the string values have to be quoted by double or single quotes.
    PHP Code:
    $sql3 "Select * From formdata WHERE loc='" $row2['loc'] . "' and lastname='" $row2['lastname'] . "' AND firstname='" $row2['firstname'] . "'"
    For better practice, always put all the array indexes inside single or double quotes like $row2['loc'] not $row2[loc].
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  3. #3
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Speaking from painful experience, consider renaming your result sets:

    from

    $row2
    $row3

    to something more meaningful like

    $old
    $new

    even these, which are longer I admit, but at least hint at what they contain...

    $existing_details
    $updated_details

    It'll make your code easier for you to read, and if you don't get out of the habit you will end up somewhere with vars like $row9 $row10 and not know where the hell they came from or what they are supposed to contain. Ouch. Still hurts.

    HTH


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
  •