PDA

View Full Version : simple search from dynamic drop down


gavimobile
09-12-2008, 04:10 AM
folks, i am trying to make a simple database search...
im not sure what to add to my query, also im not sure if there is any variables that im missing. please note. i have 2 tables. 1 is my main table and the other one is what i call, my dynamic table. the dynamic table just has 4 row in order to populat my drop down for one of the columns in my main table. I also want to use the dynamic table for my search. i want to choose 1 of the 4 rows and get results from my main table with my selected row.

tia
gavimobile


<?php
require_once('../includes/config.inc.php');

// query for stories
$query = sprintf("SELECT * FROM stories WHERE story_id='$upd'");
$results = mysql_query($query);
$row_upd = mysql_fetch_array($results);

// variables must be filled
if ($submit && $title && $desc && ! $update_id){

// Insert Query
mysql_query("INSERT INTO stories (story_lang, story_title, story_desc, story_date, story_num) VALUES ('$lang', '$title', '$desc', '$date', '$num')");

}elseif($submit && $update_id){

mysql_query("UPDATE stories set story_lang='$lang', story_title='$title', story_desc='$desc', story_num='$num' WHERE story_id = '$update_id' ");
}

if ($del){

// Delete Query
mysql_query("DELETE FROM stories WHERE story_id='$del'");
}


// query for stories
$query = sprintf("SELECT * FROM stories WHERE story_lang='$???' ");
$results = mysql_query($query);
$row = mysql_fetch_array($results);

?>

<html>
<head>

<title>Add Stories - Content Manager</title>

</head>

<body>

<!-- MY INSERT FORM --!>

<!-- BEGINING OF MY SEARCH FORM --!>
<form name="form2" action="<?php echo $_SERVER['PHP_SELF']; ?>" >
<select name="lang" >

<?php

// query for dynamic drop down
$query_dyn = sprintf("SELECT * FROM dyanmic_lang");
$results_dyn = mysql_query($query_dyn);
$row_dyn = mysql_fetch_array($results_dyn);

do {
?>
<option value="<?php echo $row_dyn['dyn_abv']; ?>"><?php echo $row_dyn['language']; ?></option>
<?php
}
while ($row_dyn = mysql_fetch_array($results_dyn));
?>
</select>
<input class="button" type="submit" name="search" value="Search" />
</form>
<!-- END OF MY SEARCH FORM --!>



<!-- THIS IS WHERE MY RESULTS ARE -->


</body>
</html>

lux
09-12-2008, 09:37 AM
Hey Gav

This is the type of query you'll need to use


SELECT *
FROM table_name
WHERE lower(col_name) LIKE lower('%$search%')


We are selecting all the cols
From a specified table
Where a match is found in for the search term in a chosen col

I use the lower() function to make the search case insensitive

Hit me back if you need more info

gavimobile
09-12-2008, 01:06 PM
Hey Gav

This is the type of query you'll need to use


SELECT *
FROM table_name
WHERE lower(col_name) LIKE lower('%$search%')
We are selecting all the cols
From a specified table
Where a match is found in for the search term in a chosen col

I use the lower() function to make the search case insensitive

Hit me back if you need more info


lux, thanks for the fast response! could you just take a look at this and tell me if this is correct? it doesnt seem to be working for me.



// query for stories
$query = sprintf("SELECT * FROM stories WHERE lower(story_lang) LIKE lower('%$search%')");
$results = mysql_query($query);
$row = mysql_fetch_array($results);



im going to check your other post!!!!

thanks

lux
09-12-2008, 02:06 PM
I would suggest coping the sql into you database admin program and run the query to see what error message you get, post the error message and we can work it out.

gavimobile
09-15-2008, 11:15 AM
I would suggest coping the sql into you database admin program and run the query to see what error message you get, post the error message and we can work it out.

i ran this sql in phpmyadmin and it came with no errors.

SELECT * FROM stories WHERE lower(story_lang) LIKE lower('%$search%')

is this a variable issue?

lux
09-15-2008, 01:37 PM
if the name of the variable you are passing to the sql statement if different to $search then change the name to the name of the variable you are using.

echo the variable to see if it contains a value.

gavimobile
09-15-2008, 02:16 PM
if the name of the variable you are passing to the sql statement if different to $search then change the name to the name of the variable you are using.

echo the variable to see if it contains a value.

lux sorry for giving ya a hard time...

my form has 2 fields, my dynamic dropdown which is named="lang1"
and my search/submit buttom named="search"

i try echoing both $search & $lang1 but still i fail.

im sure i will be kicking myself once i get to the bottom of this.. can u suggest anything else?

heres the code, i cleaned it really nicely for ya


<?php
require_once('../includes/config.inc.php');

// query for stories
$query = sprintf("SELECT * FROM stories WHERE lower(story_lang) LIKE lower('%$lang1%')");
$results = mysql_query($query);
$row = mysql_fetch_array($results);

// query for dynamic drop down
$query_dyn = mysql_query("SELECT * FROM dyanmic_lang");
?>


<form name="form2" action="<?php echo $_SERVER['PHP_SELF']; ?>" >
<select name="lang1" >
<?php
mysql_data_seek($query_dyn, 0);
while($results_dyn = mysql_fetch_assoc($query_dyn)){
?>

<option value="<?php echo $results_dyn['dyn_abv']; ?>"><?php echo $results_dyn['language']; ?></option>

<?php
}
?>
</select>
<input class="button" type="submit" name="search" value="Search" />
</form>

<?
do {
echo $row['story_lang'];
} while ($row = mysql_fetch_array($results));
?>

lux
09-15-2008, 02:37 PM
a couple of things

1. theres no method in your form (method="post")

2. you need to capture the submitted form value, then assign it to a varaible then you can use it in the sql statement.

e.g. $lang1 = $_POST['lang1'];

gavimobile
09-15-2008, 02:48 PM
a couple of things

1. theres no method in your form (method="post")

2. you need to capture the submitted form value, then assign it to a varaible then you can use it in the sql statement.

e.g. $lang1 = $_POST['lang1'];


yup you called it.. it was because of method=post

my lang1 var was originally called $lang but i changed it to $lang1 because i thought $lang was a var for somethign else!

anyways thanks for your time man

please mark this one as SOLVED