Query fields by reference

[Lonestar Jack]

I have an array ($arr) of arrays ($ar) in which I want to describe the columns in a report.
The salient elements include the column headings and the MYSQL field to use:
Array ( "City", 12, 29,"Y", "City_date", "City_Total" ),
Array ( "Atmos", 8, 23, "Y", "Atmos_date", "Atmos_Total" ),
etc.

The headings work well but I do not get the contents of "City_date" or "City_total"

using:

         reset($ar);
        foreach ($arr as $ar){;but to no avail.
          $d =  $ar[4];
          $a  = $ar[5];
            echo '<td><span class="blue">' . $d . '</span><br><span class="red">' . $a . '</span></td>' ;
          };, 

The result is a column with "City_date" and "City_total" in 2 lines.
I have tried using the &character. but to no avail.

[paulkd]

Hi Jack,

You're not explaining your problem too well. Looking at the code you have provided there are is no "content", just strings in arrays, which, syntax errors aside, display as expected.

[Lonestar Jack]

Paulkd;
It is hard to grasp "by reference" vs "by content".
I want line 3. ($d = $ar[4]) to deliver the data in the "City_date" column in a query field and not the contents of $ar[4], thus the "by reference".
I didn't show everything I tried, but $d = $row($ar[4]; will not work nor variations thereof.
It probably has to do with using "&" some where along the line and I am sure someone in Daniweb has the answer.
There isn't much discussion of this proble in Google or PHP documentation.

[paulkd]

Something like this?

$arr['City_date'] = 'result';
$ar = array("City", 12, 29, "Y", "City_date", "City_Total");
echo $arr[$ar[4]];

Show your mysql query please with an example of the data returned for a typical row.

[Lonestar Jack]

Here is the query and the data return for a typical row.

SELECT * FROM bills WHERE Active = 'Y'and City_total <> '' ORDER by xyear desc , xmonth desc LIMIT 0,1


Array ( [0] => 96 [Bills_ID] => 96 [1] => Y [Active] => Y [2] => Feb - 13 [Month] => Feb - 13 [3] => 201302 [period] => 201302 [4] => 2013 [XYear] => 2013 [5] => 02 [XMonth] => 02 [6] => 2013-02-15 [City_Date] => 2013-02-15 [7] => 452 [City_Elect_Usage] => 452 [8] => 53.28 [City_Elect_Amount] => 53.28 [9] => 0.1179 [City_Elect_Unit] => 0.1179 [10] => 1000 [City_Water_Usage] => 1000 [11] => 20.75 [City_Water_Amount] => 20.75 [12] => 0.0208 [City_Water_Unit] => 0.0208 [13] => 29.25 [City_Sewer] => 29.25 [14] => 4.75 [City_Storm] => 4.75 [15] => 16.00 [City_Garbage] => 16.00 [16] => 126.03 [City_Total] => 126.03 [17] => 2013-02-15 [Atmos_Date] => 2013-02-15 [18] => 6.4 [Atmos_Gas_Usage] => 6.4 [19] => 58.10 [Atmos_Gas_Amount] => 58.10 [20] => [Atmos_Gas_Unit] => [21] => 2013-02-05 [Verizon_Date] => 2013-02-05 [22] => [Verizon_Phone] => [23] => [Verizon_Direct_TV] => [24] => 20.78 [Verizon_DSL] => 20.78 [25] => 119.99 [Verizon_Triple] => 119.99 [26] => 394.26 [Verizon_Total] => 394.26 [27] => [Sprint_date] => [28] => [Sprint_Minutes] => [29] => [Sprint_Amount] => [30] => 2013-02-10 [Conrad_Date] => 2013-02-10 [31] => 120.00 [Conrad_Amount] => 120.00 [32] => 2013-02-09 [Chase_Date] => 2013-02-09 [33] => 12.090 [Chase_Amount] => 12.090 [34] => 2013-02-09 [American_Business_Date] => 2013-02-09 [35] => 214.64 [American_Business_Amount] => 214.64 [36] => [Capital_One_Date] => [37] => [Capital_One_Amount] => [38] => [Bank_Of_America_Date] => [39] => [Bank_Of_America_Amount] => [40] => [American_Personal_Date] => [41] => [American_Personal_Amount] => [42] => [Scott_White_Date] => [43] => [Scott_White_Amount] => [44] => [Travelers_Date] => [45] => [Travelers_Amount] => [46] => [Capital_End] => [47] => [Capital_One_Savings] => [48] => [First_Texas_End] => [49] => [Merrill_End] => [50] => [Wells_Primary] => [51] => [Wells_Ira] => [52] => [Wireless_date] => [53] => [Wireless_access] => [54] => [Wireless_voice] => [55] => [Wireless_data] => [56] => [Wireless_other] => [57] => [Wireless_taxes] => [58] => [Wireless_total] => [59] => [Wireless_minutes] => ) 

[paulkd]

Could you post the lines that produce the array?

Yuk! Use either mysql_fetch_assoc() or mysql_fetch_row() or declare the type of array with mysql_fetch_array(). You don't need both do you?
If you want column 4 say, use mysql_fetch_row().

Also, there seems to be a poor schema. This HUGE table should be made up of many smaller ones IMO. Also some of your date fields are very difficult to use e.g. 'Feb - 13'.

I should say that mysqli or PDO should also be used - not mysql.

[Lonestar Jack]

Paulkd --- look at the first post because the code for 2 of the 9 associate arrays is there.

diafol --- of course I have to use a mysql_fetch... to get the data.
As for using many files --- this and several other reports would have to join 9 or more different files and need all the maintenence programs to go with them.
As for the "Feb - 13" it is a concatinated field for display only.
Wether I use PDO or MYSQLI has nothing to do with my original question.

I have been using a hard coded version to get the report and it works, but I like to run a table and array based system because they are quicker to change and lend themselves to "on the fly" type reporting.

In the long ran you haven't answered the question of how to get data fields by reference from an array. Or simply, what is the correct format to get these results using the contents of the aforementioned query results and:

Array ( "City", 12, 29,"Y", "City_date", "City_Total" ),

$d = "2013-02-15";
$a = "126.03";

I am able do this using Cold Fusion.
Thanks for your efforts

[paulkd]

Can you post your working ColdFusion code? CF is my first language :D

[Lonestar Jack]

Paulkd

Sorry, but I haven't had CF of my machines for years since I retired and had to my own purchase software. I converted all my non-commercial programs to PHP, MYSQL, and JQuery platforms.
When I startd PC programming the Allaire brothers were just kicking off their new product - Cold Fusion and it was the only higher level framework in existence. Great stuff and a real boon to programmers,

[paulkd]

OK, how about posting the bills table schema?

I've used ColdFusion since version 4.0 - remember those pcode errors?

In the long ran you haven't answered the question of how to get data fields by reference from an array.

Perhaps I misunderstood, but I assumed that you were asking how to get data via column number, e.g. $data[4] as opposed to column name (fieldname) e.g. $data['xyear']. That's why I suggested mysql_fetch_row() instead of mysql_fetch_array().

I'm sorry you didn't find my reply that useful, but you should be aware that mysql is to be deprecated - so you may need to change your code.

Looking back at your question I think paulkd seems to be on the right track if I understand it correctly. However, I don't understand the first 4 fields in the array as they don't seem to relate to any fields in the resultset.

[Lonestar Jack]

Paulkd

This is not a typical table.
I insert 12 preformatted monthly records every year and do not allow add's or delete's in any program.
As bills come in the monthly records are updated using Nu-Shere's PHPED wizard which takes all the drudgery out of coding . There are 60 fields and 15 payees in each record.
While I wrote a general propgram to display in tabuler form the schema of any table in any database it does not output a PDF file that I could share with you. I just keep the results in a popup that I can refer to when developing a new porgram if need be.
The array #4 above shows 1 record and its fields and contents.

[paulkd]

In the array you mention there are actually 120 keys.
You say your database table (bills) has 60 fields and the result of select * from bills somehow results in the array.

There is some code somewhere inbetween your SQL and the array. Without this, and more details about what you actually require ("to describe the columns in a report") I'm stuck.

[Lonestar Jack]

This code gives you one record in the query.

$sql = "SELECT * FROM bills WHERE Active = 'Y' and City_total <> '' ORDER by xyear desc , xmonth desc  linit 0,1"; 

   $result = mysql_query($sql) or die(mysql_error());   
    while($row = mysql_fetch_array($result))
          {
    echo $sql .  '<br>';     
    print_r($row); 
    exit();   
        }

I solved the problem of using the column names by switching to the query array offset. I knew this would work but prefered not to use the offset instead of th names;

Hence the Array ( "City", 12, 29,"Y", "City_date", "City_Total" ), becomes
Array ( "City", 12, 29,"Y", 6, 16),

From DB array: [City_Date] and [City_Total]
Your array: Array ( "City", 12, 29,"Y", "City_date", "City_Total" )

Notice the difference in case in the City_Date / City_date. Keys in PHP are case sensitive, so this may be causing the issue. Change to...

Your array: Array ( "City", 12, 29,"Y", "City_Date", "City_Total" )

echo $dbRecord[$columns[4]];

Should then work.

[paulkd]

I'm having trouble matching the data from your array examples to the print_r data dump you supplied. e.g. in the example where you displayed

Array ( "City", 12, 29,"Y", "City_date", "City_Total" ),
$d = "2013-02-15";
$a = "126.03";

I can match this in the print_r dump

but in your last post I cannot match

Hence the Array ( "City", 12, 29,"Y", "City_date", "City_Total" ), becomes
Array ( "City", 12, 29,"Y", 6, 16),

to the print_r dump.

Are you using actual figures or just providing example figures?

[Lonestar Jack]

Reading the print_r dump:
Remember arrays start with 0 not 1.
It shows both the the numerical and the text keys in brackets [].
To access the City_date field you can use the numerical (6) keys or the text key (City_date).
The whole purpose of my post was to have someone show me the correct code to access by text.

Array ( [0] => 96 [Bills_ID] => 96 [1] => Y [Active] => Y [2] => Feb - 13 [Month] => Feb - 13 [3] => 201302 [period] => 201302 [4] => 2013 [XYear] => 2013 [5] => 02 [XMonth] => 02

** [6] => 2013-02-15 [City_Date] => 2013-02-15 **

[7] => 452 [City_Elect_Usage] => 452 [8] => 53.28 [City_Elect_Amount] => 53.28 [9] => 0.1179 [City_Elect_Unit] => 0.1179 [10] => 1000 [City_Water_Usage] => 1000 [11] => 20.75 [City_Water_Amount] => 20.75 [12] => 0.0208 [City_Water_Unit] => 0.0208 [13] => 29.25 [City_Sewer] => 29.25 [14] => 4.75 [City_Storm] => 4.75 [15] => 16.00 [City_Garbage] => 16.00 [16] => 126.03 [City_Total] => 126.03

[paulkd]

Which key from your print_r dump contains the value 12 ?

[Lonestar Jack]

"City", 12, 29, "Y" from the $ar contain data for the first column headings.
I didn't include this because it wasn't pertinent to my central question and I didn't want to complicate the question.

[paulkd]

So $ar is manually created by you? Not from the database?

[Lonestar Jack]

Yes they are constants to describe the columns and their contents.
0. Column name for heading
1. Day of the month that bill should be received
2. Penality day for late payment
3. Whether or not to use this column
4. Date of payment
5. Amount of payment

So just changing the $ar changes the content and form of the report. I can add or change coluns by changing the #ar and not worry about the display.

[paulkd]

Sorry this is taking a while.

So for "Sprint" would your $ar array be something like:-

Array ( "Sprint", 12, 29,"Y", "Sprint_date", "Sprint_Amount" ) ?

[Lonestar Jack]

Certainly, and it would add a column to the report.

[paulkd]

Hopefully this will help you progress. Your City_date in $ar and City_Date in your database are case-mismatched.

$sql = "SELECT * FROM bills WHERE Active = 'Y' and City_total <> '' ORDER by xyear desc , xmonth desc  limit 0,1";

$result = mysql_query($sql) or die(mysql_error());


$arr = array(
    Array ( "City", 12, 29,"Y", "City_Date", "City_Total" ),
    Array ( "Atmos", 8, 23, "Y", "Atmos_date", "Atmos_Total" )
 );


while($row = mysql_fetch_assoc($result)) {

    foreach($arr as $key => $ar) {
        echo $row[$ar[4]].'<br>';
    }
    exit();
}

Your City_date in $ar and City_Date in your database are case-mismatched.

I think I mentioned that here...

http://www.daniweb.com/web-development/php/threads/463831/query-fields-by-reference#post2019890

[Lonestar Jack]

Close but no cigar:

The default collation for a character set is latin1, which is latin1_swedish_ci, and happens to be case-insensitive.

I believe that Linux uses case-sensitive table names and the collation scheme for fields.
No matter what, that does not answer the question abouthow to extract a field name from an array to use in a query result.

No - php array element names are case-sensitive, regardless of your DB names. If you are searching for the array element City_date and City_Date exists in your resultset array, you won't get a match. You should get an error (unknown index).

[Lonestar Jack]

diafol
the following code works on Windows:

select city_DATE from BIlls

On the host (Linux) it throws a error because of the table name and when that is corrected it just skips the field error with no notification.
When I correctly code the field names in the array my original problem works as first coded.
It was sloppy coding on my part in the first place. I code on my local Windows machine and upload to a Linux host hence the problem.
Thanks for your input.