?
Solved

MySQL - How to create an array

Posted on 2011-10-20
5
Medium Priority
?
241 Views
Last Modified: 2012-05-12
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']);
}
0
Comment
Question by:sam2929
5 Comments
 
LVL 22

Expert Comment

by:Om Prakash
ID: 36999160
$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
 
LVL 11

Expert Comment

by:Ovid Burke
ID: 36999195
Can you describe the error message that you get? Also explain a bit about what you are hoping to achieve with the $regionArray.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 total points
ID: 36999353
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
 

Author Comment

by:sam2929
ID: 37000743
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 37000972
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month16 days, 21 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question