SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Problem with passing results from MySQL into array

    Hi,

    Just hoping someone can shed some light on a issue i am having. I am trying to pass data held from three tables into variables via a while loop. The data is stored in 3 tables: property, client, and invoice. I am happy that my SQL works, as when I pass it direct into phpmyadmin, I am getting the full range of data retruned from the 3 tables.

    However, I seem to have an issue when it comes to passing the <client.'data'> to a variable in the while loop. The <invoice.'data'> is passed perfectly, and i can not see why the client data is different to make it not pass. I have pasted the relevant code below, pointers gratefully recieved.

    PHP Code:
        $sql "SELECT property.*, invoice.*, client.* FROM property INNER JOIN invoice ON property.id = invoice.id INNER JOIN client ON client.name = property.client WHERE property.client = '$client' AND invoice.dateinvoice != '' AND invoice.datepaid IS NULL AND invoice.sumId = 0 AND client.name='$client'";
        echo 
    'SQL = '.$sql;
        
    $result mysqli_query($link$sql);

        while (
    $row mysqli_fetch_array($result))
        {
            
    $updates[] = array(
             
    'property.House_name' => $row['House_name'],
             
    'property.house_number' => $row['house_number'],
             
    'property.addline1' => $row['addline1'],
             
    'property.addline2' => $row['addline2'],
             
    'property.town' => $row['town'],
             
    'property.county' => $row['county'],
             
    'property.postcode' => $row['postcode'],
             
    'property.fee' => $row['fee'],
             
    'property.client' => $row['client'],
             
    'property.id' => $row['id'],
             
    'invoice.invoiceno' => $row ['invoiceno'],
             
    'invoice.vatpercent' => $row ['vatpercent'],
             
    'invoice.dateinvoice' => $row ['dateinvoice'],
             
    'client.company' => $row ['clientname'],
             
    'client.department' => $row ['clientdepartment'],
             
    'client.add1' => $row ['clientadd1'],
             
    'client.add2' => $row ['clientadd2'],
             
    'client.town' => $row ['clienttown'],
             
    'client.county' => $row ['clientcounty'],
             
    'client.postcode' => $row ['clientpostcode']);
        }
        
    print_array($_POST);
        
    print_array($updates); 

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    How are you trying to retrieve said data? print_array is not a standardized PHP function. (print_r is)

  3. #3
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ahh i should have said print_array is a self made function, pretty much the same as print_r but with line breaks, I use it for debugging. I've linked a example of its output below, also should show the lack of value for client.'data'!

    Array
    (
    [0] => Array
    (
    [property.House_name] =>
    [property.house_number] => *****
    [property.addline1] => *****
    [property.addline2] =>
    [property.town] => *****
    [property.county] => *****
    [property.postcode] => *****
    [property.fee] => ***
    [property.client] => *****
    [property.id] => 1
    [invoice.invoiceno] => 208
    [invoice.vatpercent] => 0
    [invoice.dateinvoice] => 2011-08-24
    [client.company] =>
    [client.department] =>
    [client.add1] =>
    [client.add2] =>
    [client.town] =>
    [client.county] =>
    [client.postcode] =>
    )

    the stars are data i dont want displayed to the world!

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    No problem on the not-displayed bit

    have you tried print_array'ing your $row inside the loop? There's a fair chance your mySQL query isnt returning what you're expecting it to return....

  5. #5
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just tried as you suggested, and same result as my reply above.

    On reflection, I think I may get the data from the 'client' table in a seperate SQL statement, and not use a while loop for this particular data. As unlike the property and invoice table data which will return multiple different datasets, the client data will be the same throughout!

    Still not sure why the above will not work, but will work around! thanks for trying!

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    72 Post(s)
    Tagged
    0 Thread(s)
    If the client data is going to be the same for every row, yes, you're better off separating the queries to reduce redundancy (Rudy will probably come along and tell me I'm wrong there, but nyeh.)

  7. #7
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,032
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Based on the while loop (for the field names):

    PHP Code:
    $sql "
        SELECT
              property.House_name
            , property.house_number
            , property.addline1
            , property.addline2
            , property.town
            , property.county
            , property.postcode
            , property.fee
            , property.client
            , property.id
            , invoice.invoiceno
            , invoice.vatpercent
            , invoice.dateinvoice
            , client.clientname
            , client.clientdepartment
            , client.clientadd1
            , client.clientadd2
            , client.clienttown
            , client.clientcounty
            , client.clientpostcode
        FROM
            property
        INNER JOIN
            invoice
                ON property.id = invoice.id
        INNER JOIN
            client
                ON client.name = property.client
        WHERE
            property.client = '
    $client'
                AND invoice.dateinvoice != ''
                AND invoice.datepaid IS NULL
                AND invoice.sumId = 0
                AND client.name='
    $client'
    "
    ;
    echo 
    'SQL = '.$sql;
    $result mysqli_query($link$sql);

    $updates=array();

    while (
    $row mysqli_fetch_array($result)) {
        
    $updates[]=$row;
    }
    print_array($updates); 
    You'll probably want to test the query in the above to make sure it works.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    (Rudy will probably come along and tell me I'm wrong there, but nyeh.)
    no i won't
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    689
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    The basic problem is that your client table has columns with names like 'name', 'department' etc not clientname,clientdepartment. You might consider something like:
    SELECT client.name AS clientname,

  10. #10
    SitePoint Enthusiast
    Join Date
    Sep 2010
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ahundiak View Post
    The basic problem is that your client table has columns with names like 'name', 'department' etc not clientname,clientdepartment. You might consider something like:
    SELECT client.name AS clientname,
    I agree that with hindsight i would have named it differently, however i am much to far down the path now and can not change anything like that without major work to my code throughout.

    The bit about my OP was that it works for all 'property' table fields and 'invoice' table fields, however the same syntax is not working for the 'client' table data. EVEN though the SQL delivers all fields from all tables as needed when run direct against the database using phpMyAdmin!

    I have since worked around it now.

  11. #11
    SitePoint Member
    Join Date
    Sep 2006
    Location
    Thessaloniki, Greece
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hey!

    This may be out of topic but I'm itching to post it anyway

    Why have an array like that:

    Array
    (
    [0] => Array
    (
    [property.House_name] =>
    [property.house_number] => *****
    [property.addline1] => *****
    [property.addline2] =>
    [property.town] => *****
    [property.county] => *****
    [property.postcode] => *****
    [property.fee] => ***
    [property.client] => *****
    [property.id] => 1
    [invoice.invoiceno] => 208
    [invoice.vatpercent] => 0
    [invoice.dateinvoice] => 2011-08-24
    [client.company] =>
    [client.department] =>
    [client.add1] =>
    [client.add2] =>
    [client.town] =>
    [client.county] =>
    [client.postcode] =>
    )

    and not an array like that:

    Array
    (
    [0] => Array
    (
    'Property' => array(

    'House_name' => .. ,
    'house_number' => .. ,
    'addline1' => .. ,
    'addline2' => .. ,
    'town' => .. ,
    'county' => .. ,
    'postcode' => .. ,
    'fee' => ..,
    'client' => .. ,
    'id' => .. ,
    ),
    'Invoice' => array(

    'invoiceno' => .. ,
    vatpercent => .. ,
    dateinvoice => .. ,
    ),
    'Client' => array(
    company => .. ,
    department => .. ,
    add1 => .. ,
    add2 => .. ,
    town => .. ,
    county => .. ,
    postcode => .. ,
    )
    ))


    I understand from your query that for each property has an invoice which is linked to only one client. Let's say now for each property we have more that one invoices or for each property more than one Client was involved (which is a very plausible scenario when working with databases and application bussiness rules change all the time). Then the array (for example) would look like this:

    Array
    (
    [0] => Array
    (
    'Property' => array(

    'House_name' => .. ,
    'house_number' => .. ,
    'addline1' => .. ,
    'addline2' => .. ,
    'town' => .. ,
    'county' => .. ,
    'postcode' => .. ,
    'fee' => ..,
    'client' => .. ,
    'id' => .. ,
    ),
    'Invoice' => array(

    [0] => array(

    'invoiceno' => .. ,
    vatpercent => .. ,
    dateinvoice => .. ,
    ),

    [1] => array(

    'invoiceno' => .. ,
    vatpercent => .. ,
    dateinvoice => .. ,
    ), ....

    ),
    'Client' => array(
    company => .. ,
    department => .. ,
    add1 => .. ,
    add2 => .. ,
    town => .. ,
    county => .. ,
    postcode => .. ,
    )
    ))

    etc. That way you "map" the table to a "model" (table property to model Property) and you can do all sorts of stuff like defining a "schema" to each model (that maps to the fieldnames of the tables, or even assigned names as ahundiak suggested), following the MVC pattern.


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
  •