MySQL - How to create an array

Hello,
Getting error while retrieving data from Mysql table and save it into array as have to write this code in stored procedure..

$sql = "SELECT region_cd, region_title_en FROM region";
$result = mysql_query($sql);
$regionArray = array();
while($row = mysql_fetch_assoc($result)){
  array_push($regionArray,$row['region_cd']);
  array_push($regionArray,$row['region_title_en']);
}
sam2929Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
I presume you actually want to do this:
$sql = "SELECT region_cd, region_title_en FROM region";
$result = mysql_query($sql);
$regionArray = array();
while($row = mysql_fetch_assoc($result))
{
  $regionArray[$row['region_cd']] = $row['region_title_en'];
}

Open in new window

0
 
Om PrakashCommented:
$sql = "SELECT region_cd, region_title_en FROM region";
$result = mysql_query($sql);
while($row = mysql_fetch_array($result))
{
$array[] = $row['region_cd']
}

//looping through the array...
foreach($array as $id)
{
   //$id
}
0
 
Ovid BurkeConsultant InstructorCommented:
Can you describe the error message that you get? Also explain a bit about what you are hoping to achieve with the $regionArray.
0
 
sam2929Author Commented:
Thanks for the code but still does not work as I am getting error 1064 Syntax Error near line where $sql is defined:

I am trying to create a stored procedure that takes two parameters and returns the result. It works fine but when I  add the code to retrieve data from region table, it does not work..
I want to retrieve region table data into resultset and pass this resultset to crystal reports upon executing stored procedure in crystal reports...
I already know how to execute stored procedure with parameters except resultset.
Any help would be appreciated.

CREATE PROCEDURE `OT_Report_Dynamic`(IN p_startDate DATE, IN p_endDate DATE)
BEGIN

$sql = "SELECT region_cd, region_title_en FROM region";
$result = mysql_query($sql);
$regionArray = array();
while($row = mysql_fetch_assoc($result))
{
  $regionArray[$row['region_cd']] = $row['region_title_en'];
}

SELECT ot_rate.employee_ot_rate_id,ott.employee_overtime_id,ot.employee_id,
ot_rate.ot_hours AS overtime_hours, resp.*,region.*
FROM   employee_overtime ot
join overtime_transaction ott on  ot.employee_overtime_id = ott.employee_overtime_id
 join transaction t on (ott.transaction_id = t.transaction_id AND t.transaction_is_valid = 'Y')
 join  hr_respcentre resp on t.resp_centre_cd = resp.resp_centre_cd
 join  hr_region region  on resp.region_cd = region.region_cd
where (ot.date_dialy_change  >= p_startDate ) AND (ot.date_dialy_change <= p_endDate);

END $$
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as I wrote: the code you posted initially is PHP, and is nothing about database stored procedures ...

the issue I have now is that I don't understand in how far you want this query:
SELECT region_cd, region_title_en FROM region

to be reflected in the actual big query, as in that query, you have already "joined" to the region table?

maybe you clarify with data samples what you need?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.