Page 1 of 1

RE : Returning data from database....

Posted: Sun Oct 03, 2004 6:01 am
by Mark Bowen
Hi there,

Thanks to the absolutely excellent help from two people on this board I more or less now have everything working the way I need it but I just have one more small question.

Sorry for posting this in a new post as I wasn't sure if anyone was getting my replies to the original post as it now has [SOLVED] in front of it.

What I have is a piece of code (generously given to me by the aforementioned people) which takes a database table, returns all the field names and definitions so that I can use this data from within a Flash movie to build a load of checkboxes. I then want to be able to click on a button and send a search string to a PHP file such as :

http://www.mySite.com/search.php?colour ... ength=long

and use these variables to search a database table. This can all be done I know but what I really want to do is not have to write out the code for every single field I am checking against.

The code I have so far is this below :

Code: Select all

<?
$queryStart = "SELECT * FROM myDatabaseTable WHERE ";

// Open our connection to the database....
$dbh = mysql_connect("localhost", "userName", "password");
mysql_select_db("myDatabase");


// Set our query....
$sql = "SELECT * FROM myTable";
$result = mysql_query($sql) or die(mysql_error());

// Set up a $count variable....
$count = 0;
// create an array with field names we don't want to output
$excluded_fields = array('id', 'name');

while ($row = mysql_fetch_assoc($result)){
foreach ($row as $key => $value) {
// check that the $key (the field name) isn't listed in the $excluded_fields
// array so we only output fields we want
if (!in_array($key, $excluded_fields)) {

// PART I AM HAVING TROUBLE WITH IS BELOW
$output = $value . " LIKE " . '$value' . " AND ";
// END TROUBLE !!
$queryMiddle .= $output;
}
}

// increment $count by 1
$count++;
}
$queryEnd = "emptyField IS NULL";

$databaseQuery = $queryStart.$queryMiddle.$queryEnd;
echo "<b>Database Query - </b><br><br>" .$databaseQuery;
echo "<br><br>";
echo "<b>Results -</b><br><br>";


// ****************************************************************************/
// PART TWO
// USE OUR QUERY TO QUERY ANOTHER TABLE
$sql = $databaseQuery;
$result = mysql_query($sql) or die(mysql_error());

// Set up a $count variable....
$count = 0;
// create an array with field names we don't want to output
$excluded_fields = array('fieldOne', 'fieldTwo');

while ($row = mysql_fetch_assoc($result)){
foreach ($row as $key => $value) {
// check that the $key (the field name) isn't listed in the $excluded_fields
// array so we only output fields we want
if (!in_array($key, $excluded_fields)) {

$output = $key.$count.'='.$value . "&";
echo $output;
}
}

// increment $count by 1
$count++;
}


?>
The part I am having trouble with is the bit where it says // PART I AM HAVING TROUBLE WITH as I am trying to get it to build the SELECT query for me and I want the second $value to be the value that is being passed in from the URL passed in each case.

If anyone can help me with this then I would be exceptionally greatful.


Regards,

Mark Bowen

Posted: Sun Oct 03, 2004 9:29 am
by feyd
which of the url arguments is this second $value supposed to be? From the looks of it, the query you are creating will return zero records always; unless there's only one row in "myTable."

Posted: Mon Oct 04, 2004 3:25 am
by twigletmac
Had a look at your code and think I can see where you're going wrong - the query string information is in the $_GET array:

Code: Select all

<?php

// Open our connection to the database....
// it's important to have error handling on database calls so we add some
// or die() statements.
@mysql_connect('localhost', 'userName', 'password') or die(mysql_error());
@mysql_select_db('myDatabase') or die(mysql_error());

// To build the query conditions we need to gather the information from
// the $_GET array (this is where URL information is stored)

// create an array with URL variables we don't want
$excluded_url_values = array();

// set up an empty array to fill with query conditions
$sql_conditions = array();
// loop through the query string (URL) data
foreach ($_GET as $key => $value) {
	// check that the $key (the field name) isn't excluded
	if (!in_array($key, $excluded_url_values)) {
		// $key must match the fieldname in the db table
		$sql_conditions[] = $key." LIKE '".$value."'";
	}
}

// combine the array of query conditions into one list
$sql_conditions = implode(' AND ', $sql_conditions);

// It can be useful to setup the query in managable chunks (I like using
// arrays for this
$sql = array();
$sql[] = "SELECT *";
$sql[] = "FROM myDatabaseTable";
$sql[] = "WHERE ".$sql_conditions." AND emptyField IS NULL";

$sql = implode(' ', $sql);

echo '<p><b>Database Query - </b></p>'.$sql;
echo '<p><b>Results -</b></p>';

// now query the database
$result = mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');

// Set up a $count variable....
$count = 0;
// create an array with field names we don't want to output
$excluded_fields = array('fieldOne', 'fieldTwo');

while ($row = mysql_fetch_assoc($result)){
	foreach ($row as $key => $value) {
		// check that the $key (the field name) isn't listed in the $excluded_fields
		// array so we only output fields we want
		if (!in_array($key, $excluded_fields)) {
			$output = $key.$count.'='.$value . "&";
			echo $output;
		}
	}
	
	// increment $count by 1
	$count++;
}

?>
Hope this helps,
Mac

twig

Posted: Mon Oct 04, 2004 3:37 am
by phpScott
twig is the man!!

when you pass variables from one page to the other you can use get or post. If you create the url string like you did.
http://www.mysite.com/search.php?color=true&length=long
it will automatically come through as a get super variable.

look at [php_man]reserved.variables[/php_man] for an explanation of them all.

pay attention to post and get as those are the two you will use most.

phpscott

Re: twig

Posted: Mon Oct 04, 2004 3:39 am
by twigletmac
phpScott wrote:twig is the man!!
Girl, twig is the girl :P ...

Mac

apoligy

Posted: Mon Oct 04, 2004 3:53 am
by phpScott
BIG apologyI'm sorry :oops: I should have known better and remeberd that post from a few years back where that was all gone over.

'grovelling'Please don't ban me I won't have life without this forum.
'down on knee's' sorry :oops: :oops:

I'll blame it on still gettingn used to the UK keyboard.

Posted: Mon Oct 04, 2004 4:18 am
by twigletmac
:lol:

Posted: Mon Oct 04, 2004 5:24 am
by Mark Bowen
Hi Mac,

You are an absolute star! I tried the code out and with only an exceptionally tiny tweak got it to work (I think!). I just need to do a load more tests on it to make sure it is pulling data from the database correctly but I am pretty sure it will work. It surely made my mess of code shut up and listen :).

Once again, thanks to everyone who has helped me out with this. I don't know how I can ever repay you all but I will do my best by trying to read up on as much of this as I can so I won't have to bug you in future for what must seem like petty things for masters like yourselves.

Once again, thank you immensely!!

Mark

best I can think off

Posted: Mon Oct 04, 2004 5:40 am
by phpScott
The best way to thank me anyway is to help other people and pass along the gossple of php to everyone you meet.8)

That is what this forum is for to get help or to help those who need it if you know how to solve the problem.

Every one has to start some where.

Posted: Mon Oct 04, 2004 12:36 pm
by Mark Bowen
Thanks!

I will most definitely pass on the word and I will let everyone in the Flash community know about this code as I am sure it will really help out everyone there aswell. I won't forget to credit the correct people, don't worry about that.

Once again thanks for all your help everybody, I really appreciate it and have learnt quite a bit that I didn't already know. Must go and learn the whole of the manual from http://www.php.net at some point!!!


Thanks,

Mark