SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Sep 2004
    Location
    NH
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrays and multiple SQLs (maybe)

    Hi All.

    We have a web based app, that allows parents to login and check on thier kids (attendance, visits to the princiapl, grades, etc.) Well, a parent may have more than one kid in the school, so we want to present them with a list of their kids, and they can clikc on whatever kid they want to get info on. When a parent logs in, we check a "users" table for that parent, and they grab their relevant ID and off they go.

    So when we build the list of kids to display them, the SQL goes (in everyman's englisg translation here): "SHOW me all KIDS WITH PARENT ID [parent id]". Well, here's the problem. If a parent has three kids, she will have 3 parent ID entries in the user table, and it appears that upon loggin in, she simply grabs the first ID. So the SQL only ever returns 1 kid.

    So, now I need a SQL that goes kinda like this: "SHOW ME ALL IDS associated with PARENT", then "SHOW ME ALL KIDS WITH PARENT ID [p1]" then "SHOW ME ALL KIDS WITH PARENT ID [p2]" etc. until we run out of parent IDs. And to complicate it further, we present the list as a clickable HTML List of kids:

    <div id="Content">
    <h1>Parents Main Menu</h1>
    <p>Please choose a student</p>
    <br>
    <a href="contacts_set_student.php?studentid=<? echo $studentid; ?>" class="aform"><?echo $student->studentbio_fname. " " .$student->studentbio_lname; ?></a><br>

    I presume I need to build some sort of array, and then a for each loop. Any help anyone?

    Doug

  2. #2
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    NO, you need to correct your database. you need only one parent id and many kids with that parent so you need to do something like (assuming MySQL)
    parent table

    create table parent
    (
    parentId int unsinged auto_increment,
    parentName varchar(20),
    primary key(parentId)
    );

    kids table


    create table kid
    (
    kidId int unsinged auto_increment,
    kidName varchar(20),
    parentId int unsigned,
    primary key(kidId),
    foreign key(parentId) refenreces parent(parentId)
    );
    now suppose a parent has ID=2 and has 3 kids with ID=5,7,9

    parent_table
    1 || some parent name

    kid_table(there can be 10000 kids in this table with different parent ID)

    5 || name1 || 1
    6 || name2 || 18
    7 || name3 || 1
    9 || name4 || 1

    now suppose a parent with ID=1 logs in then the query that you will need be

    $query = "select kidId,ikdName from kid where parentId=1";

    and off you go


    hope this helps
    ---------------------------
    Errors = Improved Programming.
    My Site

  3. #3
    SitePoint Member
    Join Date
    Sep 2004
    Location
    NH
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    You are correct

    I was trying to take a shortcut. Thanks for the slap in the head.

    I know that if I do it right, it will be able to work a lot better down the road. Thanks!

  4. #4
    Umm. PHP Guru....Naaaah jaswinder_rana's Avatar
    Join Date
    Jul 2004
    Location
    canada
    Posts
    3,193
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, if you were thinking about shortcut then this one is better than the one you were about to take.

    and again, don't wanna look good or something, but just 2 cents, shortcuts are good but not at the expense of functionality and clarity.
    ---------------------------
    Errors = Improved Programming.
    My Site


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
  •