Problem with passing results from MySQL into array

PHP
1

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.

	$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

How are you trying to retrieve said data? print_array is not a standardized PHP function. (print_r is)

3

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

No problem on the not-displayed bit :wink:

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

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

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

Based on the while loop (for the field names):

$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.

8

no i won’t :cool:

9

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

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

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' =&gt; .. ,
           'house_number' =&gt; .. ,
           'addline1' =&gt; .. ,
           'addline2' =&gt; .. ,
           'town' =&gt; .. ,
           'county' =&gt; .. ,
           'postcode' =&gt; .. ,
           'fee' =&gt; ..,
           'client' =&gt; .. ,
           'id' =&gt; .. ,
 ),
'Invoice' =&gt; array(

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

))

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' =&gt; .. ,
           'house_number' =&gt; .. ,
           'addline1' =&gt; .. ,
           'addline2' =&gt; .. ,
           'town' =&gt; .. ,
           'county' =&gt; .. ,
           'postcode' =&gt; .. ,
           'fee' =&gt; ..,
           'client' =&gt; .. ,
           'id' =&gt; .. ,
 ),
'Invoice' =&gt; array(

      [0] =&gt; array( 
            
           'invoiceno' =&gt; .. ,
            vatpercent =&gt; .. ,
            dateinvoice =&gt; .. ,
       ),

        [1] =&gt; array( 
            
             'invoiceno' =&gt; .. ,
            vatpercent =&gt; .. ,
            dateinvoice =&gt; .. ,
       ), ....

),
'Client' =&gt; array(
            company =&gt; ..  ,
            department =&gt; ..  ,
            add1 =&gt; ..  , 
            add2 =&gt; ..  ,
            town =&gt; ..  ,
            county =&gt; ..  ,
            postcode =&gt; ..  ,
 )

))

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.