[RESOLVED] Form / SQL help

[snoopgreen]

Hello Everyone

Im new to this forum just signed up in last couple minutes
reason is i have problem with some PHP code im working on

At present i have a search form which works for searching programme title
but i need it to work for all or either of the text fields

eg. there is 3 fields Programme_title, Programme_type and Programme_mode

the user may not know all of the fields so needs to be able to search via say title and mode or title, mode and type or just type or just mode etc
basically all ways apart from a blank value

The code i have been working on is given below:

==========================================================
<?php
$db = mysql_connect("database","username","password");
mysql_select_db("username",$db);

if ($prog_title) {
$result = mysql_query("SELECT * FROM programme WHERE ",$db);
{
if (prog_title <> "") THEN
mysql_query = (mysql_query . "prog_title= '"$prog_title"'");

}

if (!$rec = mysql_fetch_array($result)) {
echo ("Sorry, no records found");
}
else {
do {
echo("Programme Title:" . $rec["prog_title"] . "<br>\n");
echo("Programme UCAS: " . $rec["prog_ucas"] . "<br>\n");
echo("Programme Type: " . $rec["prog_type"] . "<br><br>\n");

 }

While ($rec = mysql_fetch_array($result));
}
}

?>

Ive been told that i need to use if, if else statements after the SQL query
which i have started doing but dont understand really how to get it working!

Im hoping someone may me kind enough to pass some advice / help my way it will be greatly appriecated
I dont know if the form is needed to correctly understand my code, but if is please post and i will put the form code on

thanks for any help / advice in advanced greatly appriecated as mentioned
snoopgreen

[bodzan]

if ($prog_title) {
     $mq = "SELECT * FROM programme WHERE ";

 if (prog_title <> ""){
      $mq .= "prog_title= '".$prog_title."'";
 }
}

$result = mysql_query($mq,$db);

Am I right to assume this?

[snoopgreen]

hello

yeah i think thats sort of what im trying to do

but wouldnt that just work for the prog_title search
since
at top of code ive got
if ($prog_title) {

coz i ve been trying to work it out and at present i have taken the above line out.

or would i just make the other statements the same as the one you have posted?

the lines i have come up with at present are below:

//if ($prog_title) {
$result = mysql_query("SELECT * FROM programme WHERE ",$db);

if ($prog_title <> "") {
	mysql_query == mysql_query . " prog_title = '" . $prog_title . "'";
//else mysql_query == mysql_query . "prog_title = "%";
}
if ($prog_type <> "") {
	mysql_query == mysql_query . " prog_type = '" . $prog_type . "'";
}

I havent tested them yet, but im not getting any errors at present
Is this just the same as your code?
coz you have used
$mq .= "prog_title= '".$prog_title."'";

and i have used
mysql_query == mysql_query . " prog_title = '" . $prog_title . "'";

I dont know if the codes are the same?

thanks for the help any more would be great
thanks again
snoopgreen

[bodzan]

I assumed that the first if statement is the main reason when you do the query... If you don't want it to work only for that case just delete the if statement. I was just trying to make a different point I can see now I failed...

In the code I posted you will see that the actual MySQL query takes place at the end and not in the middle of the way... i.e:

$result = mysql_query("SELECT * FROM programme WHERE ",$db);//<-- THIS IS A PLACE WHERE QUERY TAKES PLACE  IN YOUR CODE
$result = mysql_query($mq,$db);//<-- THIS IS A PLACE WHERE IT TAKES PLACE IN MY EXAMPLE

Basically the code I presented should do something you want and that is to make a query to a database different according to the certain terms you check with your if statements... i.e:

if(condition){
     //basic query
}
if(another condition){
     //addition to the query
}
//run the query

You cannot do it like this:

if(condition){
     //run partial basic query
}
if(another condition){
     //addition to the query
}

It simply will run the same query to the MySQL database everytime...

Also: [man]mysql_query[/man] is a PHP function that sends query to the MySQL database NOT a contsant that you can use in the manner you shown. You will notice that I used $mq variable to create a query that I will later on run...

So in brief:

mysql_query == mysql_query . " prog_title = '" . $prog_title . "'";//<-- WILL RETURN AN ERROR
$mq .= "prog_title= '".$prog_title."'";//<-- WILL ADD THE prog_title STATEMENT TO THE $mq QUERY

Also == will check if the left side of the statement is equal to the right side and .= will add the right side to the previous content of the left side variable...

Did I make it any sense with this?

[snoopgreen]

Hello Just read your reply

Yeah i understand what you are telling me and i will try and implement it into my code

thanks for all your help greatly appriecated
snoopgreen

ps. i will probably not implement it right first time round, so i mite be back on here asking for more help / advice lol
but i will try and thanks again for your help

[bodzan]

Just remember to mark this thread resolved if it is and to start a new one if a question has nothing to do with this issue...

Come back as often as you like... We all learned these PHP thingies through this forum...

[snoopgreen]

Hello Bodzan

i have been working on my query, but i still cant get it to work properly
The code i have been working on is shown below:

$mq =  "SELECT * FROM programme WHERE ";

if ($prog_title <> "") {
	$mq .= " prog_title = '" . $prog_title . "'";
}
if ($prog_type <> "") {
	$mq .= " prog_type = '" . $prog_type . "'";
}

$result = mysql_query($mq, $db);
While ($rec = mysql_fetch_array($result)) {

echo("Programme Title:" . $rec["prog_title"] . "<br>\n");
echo("Programme UCAS: " . $rec["prog_ucas"] . "<br>\n");
echo("Programme Type: " . $rec["prog_type"] . "<br><br>\n");
}

The code above works when i put a valid prog_title in (runs and outputs the correct info)
but when i try to run the query just by prog_type or by putting values into both fields it doesnt work either
the error im getting is below:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/ca5cgr/public_html/PHPBookExamples/first.php on line 19

Which i dont know why it will run part of the query, but not other parts?
I was hoping you may be able to offer some more advice / help please in what could be wrong with the code?

thanks for any help / advice in advance
and thanks for the other help / advice already given
snoopgreen

[snoopgreen]

Hello Bodzan

i have been working on my query, but i still cant get it to work properly
The code i have been working on is shown below:

$mq =  "SELECT * FROM programme WHERE ";

if ($prog_title <> "") {
	$mq .= " prog_title = '" . $prog_title . "'";
}
if ($prog_type <> "") {
	$mq .= " prog_type = '" . $prog_type . "'";
}

$result = mysql_query($mq, $db);
While ($rec = mysql_fetch_array($result)) {

echo("Programme Title:" . $rec["prog_title"] . "<br>\n");
echo("Programme UCAS: " . $rec["prog_ucas"] . "<br>\n");
echo("Programme Type: " . $rec["prog_type"] . "<br><br>\n");
}

The code above works when i put a valid prog_title in (runs and outputs the correct info)
but when i try to run the query just by prog_type or by putting values into both fields it doesnt work either
the error im getting is below:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/ca5cgr/public_html/PHPBookExamples/first.php on line 19

Which i dont know why it will run part of the query, but not other parts?
I was hoping you may be able to offer some more advice / help please in what could be wrong with the code?

thanks for any help / advice in advance
and thanks for the other help / advice already given
snoopgreen

[snoopgreen]

sorry i have posted the last reply twice by accident

[bodzan]

I believe you should do something like:

$mq = "SELECT * FROM programme";

$a=0;
if ($prog_title <> "" or $prog_type <> "") {
     $mq .= " WHERE ";
     if ($prog_title <> "") {
          $mq .= "prog_title = '" . $prog_title . "'";
          $a=1;
     }
     if ($prog_type <> "") {
          if($a==1){
               $mq .= " AND ";
          }
          $mq .= "prog_type = '" . $prog_type . "'";
     }
}


$result = mysql_query($mq, $db);
while ($rec = mysql_fetch_array($result)) {

This way you will add each segment you need when you need it...

BTW AND is the key thing you were missing...

[snoopgreen]

Hello

thanks for all your help
i have been able to get it working
but im really thankful for your help which you give me

thanks again
snoopgreen

but in one of the posts u mentioned i sud resolve this post
could you please tell me how i do this?

thanks again for your time and help

[bodzan]

...under the Thread Tools...

You are the only one who can do it...

And good luck to you with the coding and remember there will allways be someone who will help you on this forum...