supertroels Posted May 11, 2009 Share Posted May 11, 2009 I have a script that is supposed to get all entries in a certain table and output it in an associative array. Then it's supposed to be passed to a PDF creater function. All this is good and is working fine, except that the array only holds the first entry in the databse and not the rest. CODE: $result = mysql_query("SELECT * FROM $what WHERE belong_to_year = ".$year.""); while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { $i=0; foreach ($line as $col_value) { if ((mysql_field_name($result, $i) != "belong_to_year")&& (mysql_field_name($result, $i) != "pay_mgmt_chk") ) { $field=realWords(mysql_field_name($result, $i)); if (mysql_field_name($result, $i) == "date") { $array[$field] = date("d/m Y", strtotime($col_value)); $i++; } elseif (mysql_field_name($result, $i) == "amount_num") { $array[$field] = showMoney($col_value); $i++; } else { $array[$field] = $col_value; $i++; } } } } $out = array($array); print_r($out); I do not get it. Can anyone help me please? Quote Link to comment Share on other sites More sharing options...
allworknoplay Posted May 11, 2009 Share Posted May 11, 2009 I think because your $i=0 is within your WHILE loop...I think you have to put that above the loop....like this: $result = mysql_query("SELECT * FROM $what WHERE belong_to_year = ".$year.""); $i=0; while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { foreach ($line as $col_value) { if ((mysql_field_name($result, $i) != "belong_to_year")&& (mysql_field_name($result, $i) != "pay_mgmt_chk") ) { $field=realWords(mysql_field_name($result, $i)); if (mysql_field_name($result, $i) == "date") { $array[$field] = date("d/m Y", strtotime($col_value)); $i++; } elseif (mysql_field_name($result, $i) == "amount_num") { $array[$field] = showMoney($col_value); $i++; } else { $array[$field] = $col_value; $i++; } } } } $out = array($array); print_r($out); Quote Link to comment Share on other sites More sharing options...
supertroels Posted May 11, 2009 Author Share Posted May 11, 2009 Nope, not it. Does the same thing still. :-\ UPDATE: Something happens all right. When I move $i=0 outside the While loop the array contains the second entry in the database??? Quote Link to comment Share on other sites More sharing options...
allworknoplay Posted May 11, 2009 Share Posted May 11, 2009 Nope, not it. Does the same thing still. :-\ UPDATE: Something happens all right. When I move $i=0 outside the While loop the array contains the second entry in the database??? It just seems that there's a problem with the incrementing. That's why you are only getting one result into the DB, whether it's the 1st entry, 0 entry, etc.... I would step back a little and forget about updating the DB, and outputting the results so that you can see what is EXPECTED to go into the DB.... Quote Link to comment Share on other sites More sharing options...
supertroels Posted May 11, 2009 Author Share Posted May 11, 2009 Nothing is going into the DB. I am trying to pull data from DB to an array, but as you know I am only getting the first row and not the rest of them (there are around fifty or so in the specific table). I had suspected the incrementing to be the problem, but I can't see exactly what's wrong with it. Anyone? Quote Link to comment Share on other sites More sharing options...
allworknoplay Posted May 11, 2009 Share Posted May 11, 2009 Nothing is going into the DB. I am trying to pull data from DB to an array, but as you know I am only getting the first row and not the rest of them (there are around fifty or so in the specific table). I had suspected the incrementing to be the problem, but I can't see exactly what's wrong with it. Anyone? Oh my apologies.... Let me take a second look.... Quote Link to comment Share on other sites More sharing options...
Mark Baker Posted May 11, 2009 Share Posted May 11, 2009 You're fetching the array using MYSQL_ASSOC, so why not use foreach ($line as $col_key => $col_value) This gives you each column name in $col_key, so no need for the calls to mysql_field_name() Quote Link to comment Share on other sites More sharing options...
supertroels Posted May 11, 2009 Author Share Posted May 11, 2009 You're fetching the array using MYSQL_ASSOC, so why not use foreach ($line as $col_key => $col_value) This gives you each column name in $col_key, so no need for the calls to mysql_field_name() Good point. Thank you, changed it. I am not that skilled in using arrays, so advice like this is much appreciated :-) Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 11, 2009 Share Posted May 11, 2009 What does the $i variable do? Seems useless to me. Can you tell me what columns your table have? Quote Link to comment Share on other sites More sharing options...
supertroels Posted May 11, 2009 Author Share Posted May 11, 2009 What does the $i variable do? Seems useless to me. You're right. It was useless. As I said: "not that skilled" Can you tell me what columns your table have? id, date, descript, payee, amount_num, pay_mgmt_chk and belong_to_year The code now looks like this, but with the same problem: $result = mysql_query("SELECT * FROM $what WHERE belong_to_year = ".$year.""); while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { foreach ($line as $col_key => $col_value) { if (($col_key != "belong_to_year")&& ($col_key != "pay_mgmt_chk") ) { $field=realWords($col_key); if ($col_key == "date") { $array[$field] = date("d/m Y", strtotime($col_value)); } elseif ($col_key == "amount_num") { $array[$field] = showMoney($col_value); } else { $array[$field] = $col_value; } } } } Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 11, 2009 Share Posted May 11, 2009 $result = mysql_query("SELECT id, DATE_FORMAT(date, '%m/%d %Y') AS date, descript, payee, amount_num FROM $what WHERE belong_to_year = ".$year.""); while ($row = mysql_fetch_assoc($result)) { $id = $row['id']; $date = $row['date']; $descript = $row['descript']; $payee = $row['payee']; $amount_num = $row['amount_num']; } I don't know what the function showValue does, but use the code above. You don't have to store everything in an array, do you? I'm sure you can do whatever you need to do in that loop. Quote Link to comment Share on other sites More sharing options...
supertroels Posted May 11, 2009 Author Share Posted May 11, 2009 Thank you for taking the time, but I have to output everything as an array, as the PDF function I am using, requires just that. Furthermore, I need the script to be able to adjust to different tables with different columns, so that's why I am not doing it the way you explain here. Thanks anyway :-) Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 11, 2009 Share Posted May 11, 2009 Then work on DB design first. I can't imagine you'll be adding too many columns to it. It'll just be a simple edit anyways, right? Quote Link to comment Share on other sites More sharing options...
supertroels Posted May 11, 2009 Author Share Posted May 11, 2009 Ken2k7: I think you misunderstand me. I need the entries from the table to be outputted to an array and it should be possible this way. I can't see what I am doing wrong, but to use another solution, that doesn't involve the creation of an array that holds all the data pulled, is not an option. Thanks for your help. Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted May 11, 2009 Share Posted May 11, 2009 Then put them in an array! Do you need me to do that for you? As for the other columns, change the SQL to SELECT id, DATE_FORMAT(date, '%m/%d %Y') AS date, descript, payee, amount_num FROM $what WHERE belong_to_year = ".$year." And then use a foreach loop on $row inside the while loop I have there. From there, store the values into an array. Quote Link to comment Share on other sites More sharing options...
supertroels Posted May 11, 2009 Author Share Posted May 11, 2009 Don't get pissed with me. I thought that I was doing just what you are describing, but in a way where the same script could be used on different table structures. What I do not get, is that the original solution does not work, though it (at least in my mind) makes logical sense. Obviously I can't see why your solution is easier. I am not that skilled, as I said earlier, and arrays is one of the things I can't seem to get my head around. Don't spend any more time on this, as you will only be further frustrated with me. I'll find a way to fix this. Quote Link to comment Share on other sites More sharing options...
supertroels Posted May 11, 2009 Author Share Posted May 11, 2009 I fixed it. This works the way I wanted it to. $result = mysql_query("SELECT * FROM $what WHERE belong_to_year = ".$year." ORDER BY date DESC"); while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { foreach ($row as $col_key => $col_value) { if (($col_key != "belong_to_year")&&($col_key != "pay_mgmt_chk")) { $col_key = realWords($col_key); if ($col_key == "date") { $array[$col_key] = date("d/m Y", strtotime($col_value)); } elseif ($col_key == "amount_num") { $array[$col_key] = showMoney($col_value); } else { $array[$col_key] = $col_value; } } } $out[] = $array; } Now $out holds the info i wanted it too. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.