SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    Kitty Lizard reptilianfeline's Avatar
    Join Date
    Aug 2002
    Location
    Somewhere in Cyberspace
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Selecting and viewing data from several tables using a form

    In my shopping cart I would like a way to view the orders (and have a note sent when a new order arrives). For this to work I need to select data from three tables. I also want to choose the date from a drop-down list generated from the DB (so as not to get a bunch of alternatives with "Error: no order" or something).

    So I tried to piece it together. I have an error somewhere and the select array isn't created. The argument is invalid in the foreach statement. I have been looking through the rest of the code in the system to copy and use bits and pieces, but I'm missing something somewhere.

    The funcion that selects from the db:
    PHP Code:
    function get_order($datum)
    {
        
    $conn db_connect();
        
    $query "select * from order_bok, orders, bocker where  
            orders.datum = '
    $datum' and order_bok.antal = '$antal
            and orders.lev_namn = '
    $lev_namn' and orders.lev_adress = '$lev_adress
            and orders.lev_postnr = '
    $lev_postnr' and orders.lev_ort = '$lev_ort
            and orders.lev_land = '
    $lev_land' and bocker.titel = '$titel' and 
            order_bok.orderid = orders.orderid and bocker.bokid = order_bok.bokid"
    ;
        
    $result = @mysql_query($query);
        if (!
    $result)
            return 
    false;
        else
            return 
    true;

    The output functions from output_fns.php
    PHP Code:
    function display_order_form($order_array)
    {
    if (!is_array($order_array))
      {
         echo "<br />Det finns inga order.<br />";
      }
    ?>
    <form method="post" action="show_order.php">
    <table>
    <tr><th colspan="2">V&auml;lj datum</th></tr>
    <tr>
    <td>Datum:</td>
    <td><select>
    <?php 
    foreach ($order_array as $row)
    {
        echo 
    '<option>'$row.'</option>';
    }
    ?>
    </select></td>
    </tr><tr>
    <td colspan="2"><input type="submit" value="H&auml;mta order"></td></tr>
    </table>
    </form>
    <?php
    }

    function 
    display_order($order_array)
    {
    ?>
    <table>
    <tr><th colspan="2">Order</th></tr>
    <tr><td>OrderID</td><td>Namn</td><td>Adress</td><td>Postnummer</td><td>Ort</td></tr>
    <tr><td><?php echo $orderid;?></td>
    <td><?php echo $lev_namn;?></td>
    <td><?php echo $lev_adress;?></td>
    <td><?php echo $lev_postnr;?></td>
    <td><?php echo $lev_ort;?></td></tr>
    <tr><td colspan="5">Bok</td></tr>
    <tr><td colspan="4"><?php echo $titel;?></td>
    <td><?php echo $antal;?> st.</td></tr>
    </table>
    <?php
    }
    show_order.php
    PHP Code:
    <?php
      
    include ('book_sc_fns.php');
      
    session_start();

      
    $orderid $_POST['row'];
      
    do_html_header('Se kunders order');

      
    // get the book info out from db
      
    $order_array get_order($datum);

      
    display_order($order_array);
     

      
    // if logged in as admin
      
    if(isset($_SESSION['admin_user']))
      {
        
    display_button('index.php''fortsatt''Forts&auml;tt handla');
        
    display_button('admin.php''admin-meny''Admin Meny');
        
      }
      else 
          echo 
    'Du har inte r&auml;tt att se administrationen.'
      
      
    do_html_footer();
    ?>
    show_order_form.php
    PHP Code:
    <?php

    // include function files for this application
    require_once('book_sc_fns.php'); 
    session_start();

    do_html_header('Visa order');
    if (
    check_admin_user())
    {
      
    display_order_form($order_array);
      
    do_html_url('admin.php''Tillbaka till admin menyn');
    }
    else
      echo 
    'Du har inte r&auml;tt att se administrationen.';

    do_html_footer();

    ?>
    There are probably some things that isn't nessessary in the show_...-files because I used a couple of other files as a template.

    What I want is a table with the orderid, the name and shipping-info displayed with the name of the books ordered and the number of books for each book. I want to get this for the date I pick in the drop-down list. All of this can be pulled from three tables that are properly connected. orderid, name, date and shipping-address is found in orders. The number of books is found in order_bok, and the title is found in bocker. order_bok has both orderid and bokid, in order to connect books, orders and items.
    Reptilian Feline
    | www.reptilian-feline.net | Art | Tutorials | Pets | Music |
    Webdesign the hard way...

  2. #2
    Non-Member coo_t2's Avatar
    Join Date
    Feb 2003
    Location
    Dog Street
    Posts
    1,819
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    So your query is not returning any rows?

    --ed

  3. #3
    Non-Member coo_t2's Avatar
    Join Date
    Feb 2003
    Location
    Dog Street
    Posts
    1,819
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    First thing I'd probably do is take the "@" out of in front of the mysql_query call.

    Then in your if(!$result) condition I'd do a "echo mysql_error();"
    to see if you get anything.



    PHP Code:
    $result mysql_query($query); 
    if (!
    $result
    {    echo(
    'query failed: '.mysql_error());
          return 
    false

    If you don't get an error then things probably ain't joined up right in your table.

    --ed

  4. #4
    Kitty Lizard reptilianfeline's Avatar
    Join Date
    Aug 2002
    Location
    Somewhere in Cyberspace
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The error I get is with the foreach statement in the display_order_form function in my first post. "Warning: Invalid argument supplied for foreach() in output_fns.php"
    Reptilian Feline
    | www.reptilian-feline.net | Art | Tutorials | Pets | Music |
    Webdesign the hard way...

  5. #5
    Kitty Lizard reptilianfeline's Avatar
    Join Date
    Aug 2002
    Location
    Somewhere in Cyberspace
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I just figured out, I've been thinking wrong. I'm now adding a function to create the foreach-loop with the dates. Then the result si to be run through my get_order function. Let's see how it goes.
    Reptilian Feline
    | www.reptilian-feline.net | Art | Tutorials | Pets | Music |
    Webdesign the hard way...

  6. #6
    Kitty Lizard reptilianfeline's Avatar
    Join Date
    Aug 2002
    Location
    Somewhere in Cyberspace
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok... I have tried this and the result isn't the one I expected. What am I doing wrong?

    The function to get a list of dates when orders have been placed.
    PHP Code:
    function get_order_date()
    {
        
    $conn db_connect();
        
    $query "select datum from orders";
        
    $result mysql_query($query);
        if (!
    $result)
            return 
    false;
        
    $num_cats = @mysql_num_rows($result);
       if (
    $num_cats ==0)
          return 
    false;  
       
    $result db_result_to_array($result);
            return 
    $result;

    The function db_result_to_array
    PHP Code:

    function db_result_to_array($result)
    {
        
    $res_array = array();
        
        for (
    $count=0
            
    $row = @mysql_fetch_array($result);
            
    $count++
            )
        
    $res_array[$count] = $row;
            
        return 
    $res_array;

    show_order_forms.php
    PHP Code:
    <?php

    // include function files for this application
    require_once('book_sc_fns.php'); 
    session_start();

    do_html_header('Visa order');
    if (
    check_admin_user())
    {
        
    $order_array get_order_date();
        
    display_order_form($order_array);
        
    do_html_url('admin.php''Tillbaka till admin menyn');
    }
    else
        echo 
    'Du har inte r&auml;tt att se administrationen.';

    do_html_footer();

    ?>
    And the function display_order_forms
    PHP Code:
    function display_order_form($order_array)
    {
    if (!is_array($order_array))
      {
         echo "<br />Det finns inga order.<br />";
      }
    ?>
    <form method="post" action="show_order.php">
    <table>
    <tr><td colspan="2"><?php echo $order_array;?></td></tr>
    <tr><th colspan="2">V&auml;lj datum</th></tr>
    <tr>
    <td>Datum:</td>
    <td><select>
    <?php 

    foreach ($order_array as $row)
    {
        echo 
    '<option>'$row.'</option>';
    }
    ?>
    </select></td>
    </tr><tr>
    <td colspan="2"><input type="submit" value="H&auml;mta order"></td></tr>
    </table>
    </form>
    <?php
    }
    I echoed the $order_array to see what I get, and I get just one word - Array. Somehow the array doesn't work properly. I was expecting a list of dates. Maybe I have to add a variable?
    Reptilian Feline
    | www.reptilian-feline.net | Art | Tutorials | Pets | Music |
    Webdesign the hard way...

  7. #7
    Non-Member coo_t2's Avatar
    Join Date
    Feb 2003
    Location
    Dog Street
    Posts
    1,819
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    I just noticed with the first get_order function you posted you're not returning the result array. You're just returning true or false
    but when you call the function you are expecting it to return an array.



    Ok now you're returning an array from the db_result_to_array function.

    But when you get it you are just echoing the array, and not the values it holds.

    So in display_order_form, instead of echoing $row, you wanna do something like:
    echo $row['field_name'] ;

    --ed

  8. #8
    Kitty Lizard reptilianfeline's Avatar
    Join Date
    Aug 2002
    Location
    Somewhere in Cyberspace
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank coo! I had just read the same thing with more explanations in another forum I visit.

    I ended up adding another db_function so as not to upset the rest of the system.
    PHP Code:
    function db_result_to_array_row($result)
    {
        
    $res_array = array();
        
        
    $count 0;
        while(
    $row mysql_fetch_row($result))
        {
            
    $res_array[$count++] = $row[0];
        }
            
        return 
    $res_array;

    And changed the get_order_date function to this:
    PHP Code:
    function get_order_date()
    {
        
    $conn db_connect();
        
    $query "select distinct datum from orders";
        
    $result mysql_query($query);
        if (!
    $result)
            return 
    false;
        
    $num_cats = @mysql_num_rows($result);
       if (
    $num_cats ==0)
          return 
    false;  
       
    $db_result db_result_to_array_row($result);
            return 
    $db_result;


    So the form with the drop-down looks like this:
    PHP Code:
    function display_order_form($order_array)
    {
    if (!is_array($order_array))
      {
         echo "<br />Det finns inga order.<br />";
      }
    ?>
    <form method="post" action="show_order.php">
    <table>
    <tr><th colspan="2">V&auml;lj datum</th></tr>
    <tr>
    <td>Datum:</td>
    <td><select name="datum">
    <?php
    foreach ($order_array as $row)
    {
        echo 
    '<option>'$row.'</option>';
    }
    ?>
    </select></td>
    </tr><tr>
    <td colspan="2"><input type="submit" value="H&auml;mta order"></td></tr>
    </table>
    </form>
    <?php
    }
    The show_order_form.php file now looks right, but when I use the form I don't get anything. Show_order.php that the form sends the data to looks like this:
    PHP Code:
    <?php
      
    include ('book_sc_fns.php');
      
    session_start();

      
    $datum $_POST['$datum'];
      
    do_html_header('Se kunders order');

      
    $order_array get_order($datum);

      
    display_order($order_array);
     

      
    // if logged in as admin
      
    if(isset($_SESSION['admin_user']))
      {
        
    display_button('index.php''fortsatt''Forts&auml;tt handla');
        
    display_button('admin.php''admin-meny''Admin Meny');
        
      }
      else 
          echo 
    'Du har inte r&auml;tt att se administrationen.'
      
      
    do_html_footer();
    ?>
    And display_order looks like this:
    PHP Code:
    function display_order($order_array)
    {
    ?>
    <table>
    <tr><th colspan="2">Order</th></tr>
    <tr><td>OrderID</td><td>Namn</td><td>Adress</td><td>Postnummer</td><td>Ort</td></tr>
    <tr><td><?php echo $orderid;?></td><td><?php echo $lev_namn;?></td><td><?php echo $lev_adress;?></td><td><?php echo $lev_postnr;?></td><td><?php echo $lev_ort;?></td></tr>
    <tr><td colspan="5">Bok</td></tr>
    <tr><td colspan="4"><?php echo $titel;?></td><td><?php echo $antal;?> st.</td></tr>
    </table>
    <?php
    }
    Is my variables screwed up? Am I really posting data or is my form faulty?
    Reptilian Feline
    | www.reptilian-feline.net | Art | Tutorials | Pets | Music |
    Webdesign the hard way...

  9. #9
    Non-Member coo_t2's Avatar
    Join Date
    Feb 2003
    Location
    Dog Street
    Posts
    1,819
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by reptilianfeline

    PHP Code:
    function display_order($order_array)
    {
    ?>
    <table>
    <tr><th colspan="2">Order</th></tr>
    <tr><td>OrderID</td><td>Namn</td><td>Adress</td><td>Postnummer</td><td>Ort</td></tr>
    <tr><td><?php echo $orderid;?></td><td><?php echo $lev_namn;?></td><td><?php echo $lev_adress;?></td><td><?php echo $lev_postnr;?></td><td><?php echo $lev_ort;?></td></tr>
    <tr><td colspan="5">Bok</td></tr>
    <tr><td colspan="4"><?php echo $titel;?></td><td><?php echo $antal;?> st.</td></tr>
    </table>
    <?php
    }
    Is my variables screwed up? Am I really posting data or is my form faulty?
    You can't access variables like that in this function.
    You have to do it like:
    $order_array['order_id'] ;

    Or alternatively, you could place this at the top of your function and leave the variables the way you have them now:
    extract($order_array);

    So this might work:

    PHP Code:
    function display_order($order_array)
    {
    extract($order_array);
    ?>
    <table>
    <tr><th colspan="2">Order</th></tr>
    <tr><td>OrderID</td><td>Namn</td><td>Adress</td><td>Postnummer</td><td>Ort</td></tr>
    <tr><td><?php echo $orderid;?></td><td><?php echo $lev_namn;?></td><td><?php echo $lev_adress;?></td><td><?php echo $lev_postnr;?></td><td><?php echo $lev_ort;?></td></tr>
    <tr><td colspan="5">Bok</td></tr>
    <tr><td colspan="4"><?php echo $titel;?></td><td><?php echo $antal;?> st.</td></tr>
    </table>
    <?php
    }

  10. #10
    Kitty Lizard reptilianfeline's Avatar
    Join Date
    Aug 2002
    Location
    Somewhere in Cyberspace
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm learning by trial and error and it looks more like error than trial

    I tried what you suggested. It looks like I'm not creating an array in the first place.

    This is my db-function that I use to collect the relevant orders from the DB with.
    PHP Code:
    function get_order($datum)
    {
        
    $conn db_connect();
        
    $query "select orders.datum, order_bok.antal, orders.lev_namn, bocker.titel 
            from order_bok, orders, bocker 
            where order_bok.orderid = orders.orderid and bocker.bokid = order_bok.bokid"
    ;
        
    $result mysql_query($query);
        if (!
    $result
        {
            echo(
    'query failed: '.mysql_error());
            return 
    false;
        }
        else
            return 
    true;

    I cut it down a bit and did the same with the output-part. I'm probably thinking all wrong here. It's hard translating from what you are used to do in MsAccess to MySQL.

    Lets see if I'm thinking right...
    When I send the date to the script I want it to pull out all orders from that date (datum) and display it order by order with name (lev_namn) title (bok_titel) and number (antal). There can be more than one order for each date, so I have to use an array, right? So my HTML-table is wrong in display_order. I need it to be more flexable. To do this I have to use a foreach loop, just as I did with the date itself. Then for each OrderID I display the relavant information. Is that correct?
    Reptilian Feline
    | www.reptilian-feline.net | Art | Tutorials | Pets | Music |
    Webdesign the hard way...

  11. #11
    Kitty Lizard reptilianfeline's Avatar
    Join Date
    Aug 2002
    Location
    Somewhere in Cyberspace
    Posts
    385
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I made some changes, but the foreach and array still don't work.

    The query
    PHP Code:
    function get_order()
    {
        
    $conn db_connect();
        
    $query "select orders.orderid, order_bok.antal, orders.lev_namn, bocker.titel 
            from order_bok, orders, bocker 
            where orders.datum = '
    $datum' and order_bok.orderid = orders.orderid 
            and bocker.bokid = order_bok.bokid
            and orders.orderid = order_bok.orderid"
    ;
        
    $result mysql_query($query);
        if (!
    $result)
            return 
    false;
        
    $num_cats = @mysql_num_rows($result);
       if (
    $num_cats ==0)
          return 
    false;  
       
    $db_result db_result_to_array_row($result);
            return 
    $db_result;

    Part of show_order.php
    PHP Code:
    session_start();

      
    $datum $_POST['datum'];
      
    do_html_header('Se kunders order');

      
    $order_array get_order($datum);

      
    display_order($order_array); 
    And the output-function
    PHP Code:
    function display_order($order_array)
    {
    if (!is_array($order_array))
      {
         echo "<br />Det finns inga order.<br />";
      }
    ?>
    <table>
    <tr><th colspan="4">Order</th></tr>
    <tr><td>OrderID</td><td>Namn</td><td>Titel</td><td>Antal</td></tr>
    <?php
    foreach ($order_array as $row)
    {
        echo 
    '<tr><td>'.$order_array['orderid'].'</td>
    <td>'
    .$order_array['lev_namn'].'</td>
    <td>'
    .$order_array['titel'].'</td>
    <td>'
    .$order_array['antal'].'</td></tr>';
    }
    ?>
    </table>
    <?php
    }
    I get an invalid argument in foreach.
    Reptilian Feline
    | www.reptilian-feline.net | Art | Tutorials | Pets | Music |
    Webdesign the hard way...


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
  •