Page 1 of 1

Make array from query results

Posted: Thu Jun 23, 2005 3:16 am
by big_c147
Hi

What i want to be able to do is to connect to a database and pull off records and fields (which i have done) and then assign to an array.

What i then want to be able to do is to remove dupplicate records (i know this can be done by using array_unique). Once this is done i want to be able to print each of the fields separatly.

I have know idea how to do this, is there a example who how to store the results from a query into a array?

I have read the Array section on http://www.php.net but i dont understand it very well.

Thanks
Craig

Posted: Thu Jun 23, 2005 4:56 am
by harrison

Code: Select all

// declare
$aray = array();
// get result
$result = mysql_query($query);
// loop through it
while($data=mysql_fetch_array($result)){
  // get needed data
  $id = $data['id'];
  $name = $data['name'];
  // put to array
  // check if exists
  if(!array_key_exists($id,$aray)){
    // if not, continue
    $aray[$id] = $name;
  }
}
Read before posting.

Posted: Thu Jun 23, 2005 5:29 am
by big_c147
I have put that in and i get a "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in c:\phpdev\www\compdb\shownotes.php on line 292".

Heres is the code im using


Code: Select all

$sql= "SELECT DISTINCT * FROM tblnotes WHERE CompanyName LIKE'$com'AND Date >=$ConStart AND Date <= $ConEnd AND Title LIKE 

'%$trimm%'  ORDER by DATE DEsC";

//$result = mysql_query($sql,$odbc) or die(mysql_error());

// declare

$aray = array();
// get result

$result = mysql_query($sql);
// loop through it

while($row=mysql_fetch_array($result))
{  
// get needed data  

$id = $data['Title'];  

$name = $data['Username'];  
// put to array  
// check if exists  

if(!array_key_exists($id,$aray)){
    // if not, continue
    $aray[$id] = $name;  
}
}

Posted: Thu Jun 23, 2005 5:52 am
by harrison
the error
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource
means that you have an error in you query.
insert this after you get the result

Code: Select all

$result = mysql_query($query);
// inserted to see your error
if(!result){
  die("Query error: $query<br>".mysql_error());
}
That code will kill execution in case you have a query error an then give the error message from mysql server.

After you have done that, post the error again for me to help you more.

Posted: Thu Jun 23, 2005 6:08 am
by big_c147
hi it says:
Wrong datatype for first argument in call to array_key_exists in c:\phpdev\www\compdb\shownotes.php on line 303
Array

here is my complete code

Code: Select all

<?




?>

<html>

<head>
<meta http-equiv="Content-Language" content="en-gb">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>Search for notes</title>
<link rel="stylesheet" type="text/css" href="stylesheet.css">
</head>

<body>

<div align="center">
  <center>
  <table border="0" width="671">
    <tr>
      <td width="667" colspan="8" align="center">
        <p align="center"><img border="0" src="images\Logo.gif" width="269" 

height="90"></td>
    </tr>
    <tr>
      <td width="667" colspan="8" align="center">
        <h1>Search for Notes</h1>
      </td>
    </tr>
    <tr>
      <td align="center" width="93"></td>
      <td align="center" width="59"></td>
      <td align="center" width="71"></td>
      <td align="center" width="100"></td>
      <td align="center" width="102"></td>
      <td align="center" width="88"></td>
      <td align="center" width="45"></td>
      <td align="center" width="70"></td>
    </tr>
    <tr>
      <td align="center" width="628" colspan="8"><br>
<form method="POST" action="ShowNotes.php">

<p> Select Company <select size="1" name="CompanyName" style="left">
<option>*All Records*</option>


<?

$sql= "SELECT DISTINCT CompanyName FROM tblcomp ORDER BY CompanyName DESC";


$odbc=mysql_connect ("localhost", "", "") or die ('I cannot connect to the database because: 

' . mysql_error());	

mysql_select_db ("Competitors");


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


while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {	

echo "<option>$row[CompanyName]</option>"; 

}

mySQL_close(); 

?>
</P>
</select>

<form>

<p>Start Month

<select size="1" name="StartMonth">
<option>January</option>;
<option>February</option>;
<option>March</option>;
<option>April</option>;
<option>May</option>;
<option>June</option>;
<option>July</option>;
<option>August</option>;
<option>September</option>;
<option>October</option>;
<option>November</option>;
<option>December</option>;
</select>

Start Year

<select size="1" name="StartYear">

<?

$sql = "SELECT DISTINCT Date from tblNOTES ORDER by Date ASC";

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


while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {	

$year1 = getdate($row[Date]);
$year2 = $year1["year"];
echo "<option>$year2</option>";

}
?>

</select></P>

<p>
End Month

<select size="1" name="EndMonth">
<option>January</option>;
<option>February</option>;
<option>March</option>;
<option>April</option>;
<option>May</option>;
<option>June</option>;
<option>July</option>;
<option>August</option>;
<option>September</option>;
<option>October</option>;
<option>November</option>;
<option>December</option>;
</select>

End Year

<select size="1" name="EndYear">

<?

$sql = "SELECT DISTINCT Date from tblNOTES ORDER by Date ASC";

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


while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {	
$year1 = getdate($row[Date]);
$year2 = $year1["year"];

$yeararray = array($year2);
	
foreach ($yeararray as $year) 
{
  	
echo "<option>$year</option>";

}
}


?>

</select>
<?

?>


<p align="center"





  <p>&nbsp;

With title containing

<input type="text" name="Key" size="35" tabindex="2" style="center">&nbsp;&nbsp;&nbsp;


  </center>

  
  <center>

  
<p>To search by dates leave title search field blank </P> 
  
<p align="left"

  </p>
     
  <p align="center"><input type="submit" value="Submit" name="Submit"></p>
</form>

<?


if ($_POST['CompanyName']=="*All Records*")
{
$com = "%";
$com1=$_POST['CompanyName'];
}
else
{
$com = $_POST['CompanyName'];
$com1 = $_POST['CompanyName'];
}



$sm= $_POST['StartMonth'];
$sy= $_POST['StartYear'];
$em = $_POST['EndMonth'];
$ey = $_POST['EndYear'];
$key = $_POST['Key'];

if($sm==""||$sy==""||$em==""||$ey=="")
{
echo "<b>Please enter search criteria</b>";
exit();

}


$ConEnd = strtotime ("1 $em $ey");

$month = date(m,$ConEnd);

$days = cal_days_in_month(CAL_GREGORIAN, $month, $ey);

"$month";




$ConStart = strtotime ("1 $sm $sy");

$ConEnd = strtotime ("$days $em $ey");


//trim whitespace from the stored variable

  $trimmed = trim($key);  

//separate key-phrases into keywords

  $trimmed_array = explode(" ",$trimmed);

if($result)

{
echo"<p>";
echo"<p align=\"center\"";
echo "<p><b> $nor records found<br> Dates between 1 $sm $sy and $days $em $ey <br> </b>";
echo "<b> Selected company is $com1 <br></b>";
echo "Title contains $search1 ";

}

echo"<div align=\"center\">";
echo"<center>";
echo"<TABLE BORDER=\"0\" CELLPADDING=\"2\" CELLSPACING=\"2\"";
echo "<tr><td><div align=\"left\">";
echo"<TR>";
echo"<TD BGCOLOR=\"#99CC00\"><b>Title   </b></TD>";
echo"<TD BGCOLOR=\"#99CC00\"><b>Date   </TD></b>";
echo"<TD BGCOLOR=\"#99CC00\"><b>Company   </b></TD>";
echo"<TD BGCOLOR=\"#99CC00\"><b>Submited by   </b></TD>";
echo"</TR>";
echo"</div>";




foreach ($trimmed_array as $trimm)
{//start for-next

$sql= "SELECT DISTINCT * FROM tblnotes WHERE CompanyName LIKE'$com'AND Date >=$ConStart AND 

Date <= $ConEnd AND Title LIKE '%$trimm%'  ORDER by DATE DEsC";

//$result = mysql_query($sql,$odbc) or die(mysql_error());

// declare

$aray = array();
// get result

$result = mysql_query($sql,$odbc) or die(mysql_error());
// loop through it


while($row=mysql_fetch_array($result))
{  
// get needed data  

$id = $data['Title'];  

$name = $data['Username'];  
// put to array  
// check if exists  

if(!array_key_exists($id,$aray)){
    // if not, continue
    $aray[$id] = $name;  
}

}





$realdate = date('l dS M Y G:i:s', $date);
//echo"<TR>";
//echo"<TD>$row[Title]</TD>";
//echo"<TD>$resultarray[Title]</TD>";

//echo"<TD> $realdate </TD>";
//echo"<TD> $row[CompanyName] </TD>";
//echo"<TD>$row[Username]</TD>";
//echo"</TR>";



$plus = " or ";

if($search1 =="")
{
$search1="<b>\"$trimm\"</b>";
}
else
{
$search1 = "$search1$plus<b>\"$trimm\"</b>";



}

}//exit for-next


echo $aray;
//print_r($resultarray);


$odbc=mysql_connect ("localhost", "", "") or die ('I cannot connect to the database because: 

' . mysql_error());	

mysql_select_db ("Competitors");






echo"</center>";
echo"</table";

?>
        
        <p>&nbsp;</td>
    </tr>
  </table>
  </center>
</div>

    <tr>
      
      

    <tr>
      <td align="center" width="628" colspan="8">
</table>

</body>

Posted: Thu Jun 23, 2005 6:18 am
by harrison
Look at this part of your code:
big_c147 wrote: while($row=mysql_fetch_array($result))
You replaced $data with $row.

Otherwise, If you want to really use $row, then replace all the existence of $data to remove the error.

Posted: Thu Jun 23, 2005 6:31 am
by big_c147
Thanks... that has got rid of the error, How do i display all of the data stored in the array?

Posted: Thu Jun 23, 2005 8:02 am
by Chris Corbyn
I strongly suggest you read the manual at http://www.php.net/ if you do not know how to read values from an array.

Basically you just use $array['keyname'] like any other variable when echo()'ing or whatever.

The below will churn out everything that's contained in it... great for testing ;)

Code: Select all

<pre>
<?php
print_r($array);
?>
</pre>

Posted: Thu Jun 23, 2005 8:13 pm
by harrison
Displaying the contents of an array depends on how do you want it to be displayed. But basically, you can call it's elements either by their keyname(eg.id,name) or by their offsets(eg.0,1,2).
If you want to use offsets then, use loop contructs such as for and while.

Code: Select all

// for sample
for($i=0;$i<count($aray);$i++){
echo $aray[$i].'<br>';
}
// while sample
$i=0;
while($i<count($aray)){
echo $aray[$i++].'<br>';
}
Now if you don't want to use offsets but you don't know the keynames of each element, use foreach statement.

Code: Select all

foreach($aray as $element){
echo $element.'<br>';
}
But it is best to read it from the manual yourself. Read before post, OK?