www.webdeveloper.com
Results 1 to 5 of 5

Thread: Formatting a simple SQL query...

  1. #1
    Join Date
    Jun 2006
    Location
    Doncaster
    Posts
    78

    Formatting a simple SQL query...

    Evening...
    I'm trying to create a dynamic select drop-down box, but struggling with the SQL to create the options.

    This is what I have:
    PHP Code:
    $query "SELECT * FROM places"
         
    $result mysql_query($query) or die(mysql_error());

    while(
    $row mysql_fetch_array($result)){
        echo 
    '<option value=\"'.$row['vname'].'\">'.'$row['vname']'.'</option>';
        

    I'm sure it is pretty simple but I can't crack it. Anyone?

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,523
    You need to get rid of the quotes around the second instance of $row['vname']. An alternative that makes it easier to keep track of the HTML text/quoting would be to use printf:
    PHP Code:
    while($row mysql_fetch_array($result)){
        
    printf(
            
    '<option value="%s">%s</option>',
            
    $row['vname'],
            
    $row['vname']
       );

    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Jun 2006
    Location
    Doncaster
    Posts
    78

    Hmm, throws up more problems!

    Thanks NogDog,
    I've changed the code to refelct what you say and it isn't working.
    This is the full form:
    PHP Code:
    <form id="new_session" action="prcs_create_session.php" method="post">
          <table>
            <tr>
                <td>
                Session date
                </td>
                <td>
                    <input type="text" id="date3" name="date3">
                </td>
             </tr>
             <tr>
                 <td>
                Staff Req'd
                </td>
                <td>
                <input type="text" name="screeners" id="screeners" />
             </tr>
             <tr>
                 <td>
                Location
                </td>
                <td>
                
                    <select name="location" id="location">
               
               <?php
               
    // Make a MySQL Connection
    $conn mysql_connect ("localhost""wkho_TTX""y0qeqyi65i");
    mysql_select_db ("TTX"$conn);
    $query "SELECT * FROM venues"
         
    $result mysql_query($query) or die(mysql_error());

    while(
    $row mysql_fetch_array($result)){
        
    //echo '<option value=\"'.$row['vname'].'\">'.$row['vname'].'</option>';
        //echo "<br />";
        
    printf(
            
    '<option value="%s">%s</option>',
            
    $row['vname'],
            
    $row['vname'
            );
    }
    */
    ?>-->
                    </select>
                </td>
              </tr>
              <tr>
                  <td>
                    PCT
                </td>
                <td>
                    <select name="PCT" id="PCT">
                        <option value="doncaster">Doncaster</option>
                        <option value="rotherham">Rotherham</option>
                        <option value="barnsley">Barnsley</option>
                        <option value="bassetlaw">Bassetlaw</option>
                    </select>
                </td>
             </tr>
             <tr>
                 <td>
                Time
                </td>
                <td><p>
                  <label>
                    <input type="radio" name="time_select" value="1" id="time_select_0" checked="checked" />
                    pm - afternoon</label>
                  <br />
                  <label>
                    <input type="radio" name="time_select" value="2" id="time_select_1" />
                    evening</label>
                  <br />
                  <label>
                    <input type="radio" name="time_select" value="3" id="time_select_2" />
                    daytime</label>
                  <br />
                  <label>
                    <input type="radio" name="time_select" value="4" id="time_select_3" />
                    weekend</label>
                  <br />
                </p>            </td>
             </tr>
         </table>
         <input type="submit" value="Create session" />
         <input type="reset" value="Start again" />
      </form>
    I'm not sure if the question still belongs in this section, as the problem seems to be with the PHP not printing the HTML correctly rather than the MySQL query not running through the rows.

    FYI, vname is a field on the table venues in the database wkho_TTX.

    Please help!

  4. #4
    Join Date
    Jun 2006
    Location
    Doncaster
    Posts
    78

    Anyone?

    If anyone is still reading, this is the current version of the code. With, ahem, the pass word removed. Seems to run the HTML okay, then stops at the loop.

    Can anyone help? I'm really struggling with something that ought to be fairly straightforward...

    PHP Code:
    <?php
    /*
    session_start();
    if((!session_is_registered(mobile))||(!session_is_registered(email))){
    header("location:index.php");
    }
    */
    // Make an MySQL Connection
    $conn mysql_connect ("localhost""xxxxx""xxxxx");
    mysql_select_db ("TTX"$conn);
    echo 
    $test;
    ?>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
    <html xmlns="http://www.w3.org/1999/xhtml">
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Create new session</title>

    <link href="boxes_lg.css" rel="stylesheet" type="text/css" />
    </head>

    <body>
    <div id="container">
    <div id="positioned-element44">this is the positioned element originally called "positioned-element3"</div>
    <div id="HEADLINEBOX">this is the positioned element originally called "positioned-element2"</div>
    <div id="positioned-element39">this is the positioned element originally called "positioned-element"</div>
    <div id="positioned-element17">this is the positioned element originally called "positioned-element17"</div>
    <div id="positioned-element15"></div>
    <div id="positioned-element41">Rborder</div>
    <div id="positioned-element18">this is the positioned element originally called "positioned-element18"</div>
    <div id="positioned-element22">This is <strong>your</strong> page!<br />
    Here you can: <br />
    update your details<br />register for sessions<br />see how THT is doing
    </div>
    <div id="positioned-element25">
      <h1>The admin area</h1></div>
    <div id="positioned-element26"></div>
    <div id="positioned-element27"><a href="index.php"><strong>HOME</strong></a></div>
    <div id="positioned-element29"><a href="messaging.php">Messaging</a></div>
    <div id="positioned-element30">Session Tracker</div>
    <div id="positioned-element24">
      <?php

          
    //admin name
        
        
        //data sent from form

        
    $location=$_POST['location'];
        
    $PCT=$_POST['PCT'];
        
    $date=$_POST['date'];
        
    $time_select=$_POST['time_select'];
        
    $screeners=$_POST['screeners'];
        
    $setupby="1";
        
    $session_comments=$_POST['session_comments'];
        
        
    $sql "INSERT INTO `wkho_TTX`.`sessions` (`id`,`venue`, `PCT`,`date`,`time`,`staff`,`setupby`,`comments`) VALUES (NULL,'$location','$PCT','$date','$time_select','$screeners','$setupby','$session_comments')";

    //execute SQL statement 
    if (mysql_query($sql$conn)){
    echo 
    "Record added";
    } else {
    echo 
    "Record NOT added";
    }


    echo 
    '<h3>Thank-you.</h3> The session in '.$PCT.' on '.$session_date.' has been set up.';
    ?> 
    </div>
    <div id="positioned-element19"><strong>Target</strong></div>
    <div id="positioned-element31">Reports
    </div>
    <div id="positioned-element33">Update details</div>
    <div id="positioned-element20">this is the positioned element originally called "positioned-element19"</div>
    <div id="positioned-element21">this is the positioned element originally called "positioned-element20"</div>
    <div id="positioned-element28">this is the positioned element originally called "positioned-element21"</div>
    <div id="positioned-element34"></div>
    <div id="positioned-element35">this is the positioned element originally called "positioned-element23"</div>
    <div id="positioned-element32">this is the positioned element originally called "positioned-element23"</div>
    </div>

    </body>
    </html>

  5. #5
    Join Date
    Nov 2010
    Posts
    15
    I'm a bit rusty at this but seems your loops conidition is to run while the varible row is equal to the amount of rows. This would then never enter the loop.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles