[SOLVED] SQL statement with array?

hakeem · #1 (**permalink**) 10-30-08, 02:56 AM

hello

I need a little help on using an array data variable to query mysql db. I have a list of an array that holds string from A-Z so I want to use it in search for the initial letters on my table, though am using LIKE '$array[]%' for the query. I dont have idea on how to go using the array variable in SQL statement.

I will be glad if any further information is needed.

thank you in anticipation.

job0107 · #2 (**permalink**) 10-30-08, 07:21 AM

I am not sure I understand what you have in your array.
I am imagining two possibilities.
An array with 26 elements, each element containing a letter of the alphabet.
Or an array with x number of elements. with one of the element containing "A-Z".

The first possibility is easy to implement.
You can use regex and the RLIKE operator, no external array needed.
Example:

PHP Code:


			
"SELECT * FROM table WHERE column RLIKE '^[a-zA-Z]'"

In order to help with the second possibility, I would need to know more about the array and how the data in the array is going to be used in the query.

hakeem · #3 (**permalink**) 10-31-08, 12:41 AM

i am using the array in the sql query statement in a PHP script. i.e

$search = $_post['string'];

if $search = 'A';{
$result = select column from table where column like '$search%';
mysql_query($result); exit(); }

if $search = 'B';{
$result = select column from table where column like '$search%';
mysql_query($result); exit();}
...
continuously down to 'Z'

all am saying is how can i put the search string in an array (A-Z) and used it in the sql query at a time instead of write lot of codes.

hakeem

job0107 · #4 (**permalink**) 10-31-08, 09:33 AM

Quote:

Originally Posted by hakeem

i am using the array in the sql query statement in a PHP script. i.e

$search = $_POST['string'];

if $search = 'A';{
$result = select column from table where column like '$search%';
mysql_query($result); exit(); }

if $search = 'B';{
$result = select column from table where column like '$search%';
mysql_query($result); exit();}
...
continuously down to 'Z'

all am saying is how can i put the search string in an array (A-Z) and used it in the sql query at a time instead of write lot of codes.

hakeem

I don't understand what you are saying here.
From the looks of your code, you are only dealing with one value at a time.
The value collected by $_POST['string'].
Then you are doing a query with the value in $_POST['string'].

So what do you need an array for?

If you are using a single value each time, then the code becomes very simple.

PHP Code:


			
<?php
# Initialize the variables. #

# Please fill in all the necessary database values. #
$host = "";     # MySql host address
$userName = ""; # MySql username
$password = ""; # MySql password
$db = "";       # MySql database name
$table = "";    # MySql table name
$column = "";   # MySql table column name
# End database values. #

# This array could be filled in from the database. #
$array = array("A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z");
# End initalize array. #

$msg = "";
$query_results = "";
$output = "<div class='queryDiv'>Please select a value to query.<br />The results will be displayed here.</div><br class='clearFloat'/>";

# End initalize variables. #

if(!empty($_POST["submit"]))
{
 if($search = empty($_POST['string']) ? "" : $_POST['string'])
 {
  mysql_connect($host,$userName,$password) or die(mysql_error());
  mysql_select_db($db) or die(mysql_error());
  $result = mysql_query("select $column from $table where $column like '$search%'") or die(mysql_error());
  if(mysql_num_rows($result)==0)
  {
   $msg = "<p><span class='msg'>No records found using ' $search '.</span></p>";
   }
  else
  {
   $query_results = "<div class='queryDiv'>";
   while($row = mysql_fetch_assoc($result))
   {
    $query_results .= $row[$column]."<br />";
    }
   $query_results .= "</div><br class='clearFloat'/>";
   }
  mysql_free_result($result);
  mysql_close();
  }
 else{$msg = "<p><span class='msg'>You must select a value to query.</span></p>";}
 $output = $msg.$query_results;
 }
?>
<html>
<head>
<title></title>
<style>
.msg
{
 border:2px solid #f00;
 padding:5px;
 color:#f00;
 font-weight:bold;
 }
.queryDiv
{
 border:1px solid #000;
 padding:5px;
 float:left;
 }
.clearFloat
{
 clear:both;
 }
</style>
</head>
<body>
<form action="#" method="post">
 <select name="string">
  <option></option>
  <?php
  for($i=0;$i<count($array);$i++){echo $array[$i] == $search ? "<option SELECTED>$array[$i]</option>" : "<option>$array[$i]</option>";}
  ?>
 </select>
 <input type="submit" name="submit" value="Submit">
</form>
<?php
echo $output;
?>
</body>
</html>

hakeem · #5 (**permalink**) 10-31-08, 10:17 PM

thanks guys question resolved.