display the result of search query

scaleautostyle

hi everybody... i ran across the site and found TONS of information about what i want to do.... but i don't know what to take for my need.

here is what i want to do... a search engine for my website through the database.

you can see what i have done for now at this url

http://www.scale24-25.com/kitDB-site.php

this is the code

Expand|Select|Wrap|Line Numbers

 <?php

// include server parameters

  require('includes/configure-web.php');
 
?>
 
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>scale24-25.com</title>

</head>
 
<body>

<?php

// Make a MySQL Connection

mysql_connect(DB_SERVER, DB_SERVER_USERNAME, DB_SERVER_PASSWORD) or die(mysql_error());

mysql_select_db(DB_DATABASE) or die(mysql_error());
 
//////////////////////////////////////////////////////////////////////////////

 ?>
 
<table width="100%" border="0">

  <tr>

    <td><label>
 
        <?php        //  debut drop down  ///

$sql = mysql_query("SELECT DISTINCT scale FROM kit ");

$options = '';
 
echo '<select scale="scale"><option>Scale</option>';
 
while($row = mysql_fetch_array($sql)) {

$thing = $row['scale'];

echo '<option>'.$thing.'</option>';

}

echo '</select>';

// fin dropdown ///?>
 
    </label></td>

    <td>

<?php        //  debut drop down  ///

$sql = mysql_query("SELECT DISTINCT manufacturer_kit FROM kit ORDER BY manufacturer_kit");

$options = '';
 
echo '<select manufacturer_kit="manufacturer_kit"><option>Kit manufacturer</option>';
 
while($row = mysql_fetch_array($sql)) {

$thing = $row['manufacturer_kit'];

echo '<option>'.$thing.'</option>';

}

echo '</select>';

// fin dropdown ///?>

    </td>

    <td>

        <?php        //  debut drop down  ///

$sql = mysql_query("SELECT DISTINCT manufacturer_reel FROM kit ORDER BY manufacturer_reel");

$options = '';
 
echo '<select manufacturer_reel="manufacturer_reel"><option>Reel manufacturer</option>';
 
while($row = mysql_fetch_array($sql)) {

$thing = $row['manufacturer_reel'];

echo '<option>'.$thing.'</option>';

}

echo '</select>';

// fin dropdown ///?>

    </td>

  </tr>

  <tr>

    <td>&nbsp;</td>

    <td>&nbsp;</td>

    <td>&nbsp;</td>

  </tr>

  <tr>

    <td></td>

    <td>&nbsp;</td>

    <td>

           <?php        //  debut drop down  ///

$sql = mysql_query("SELECT DISTINCT engine_detail FROM kit ORDER BY engine_detail");

$options = '';
 
echo '<select engine_detail="engine_detail"><option><br />Engine detail</option>';
 
while($row = mysql_fetch_array($sql)) {

$thing = $row['engine_detail'];

echo '<option>'.$thing.'</option>';

}

echo '</select>';

// fin dropdown ///?>

    </td>

  </tr>

  <tr>

    <td>&nbsp;</td>

        <td><form name="input" action="kit_result.php" method="post">

Keywords: 

<input type="text" name="manufacturer_kit" value="" size="40">

<br>

<input type="submit" value="Submit">
 
</form> </td>

    <td>&nbsp;</td>

  </tr>

  <tr>

    <td>&nbsp;</td>

    <td>&nbsp;</td>

    <td>&nbsp;</td>

  </tr>

  <tr>

    <td>&nbsp;</td>

    <td></td>

    <td>&nbsp;</td>

  </tr>

</table>
 
<p>you can choose to fill one OR more field. more field is fill more accurate the result will be.</p>

<p>

  <label></label>

</p>

<p>&nbsp;</p>

<p>&nbsp;</p>

<p>&nbsp;</p>

<p>&nbsp;</p>

</body>

</html>

like you can see in my submit button i put a result.php file

it's this file i want to make but i'm stuck... really don't know how to make.

in fact you can make only 1 selection and press submit ex: you select the 1/24 scale and press submit it will return ALL kit in 1/24. If you select tamiya and honda it will return every kit honda made by tamiya etc...

also i want to see the kit number, the picture, year of production and of course the picture associate to this kit.

see below my field title of my database

kit_id = the ID of the kit 0, 1 , 2 ... auto increment
kit_number
description
manufacturer_kit
manufacturer_reel
engine_detail
year_prod_kit
year_prod_reel
type
scale
image_id

so any help will be granted.

thank's in advance

sebastien

scaleautostyle

just see the link doesn't work this is the right link

www.scale24-25.com/kitDB-site.php

thank's in advance for your help

yours

sebastien

Dormilich

Quote:

Originally Posted by scaleautostyle

it's this file i want to make but i'm stuck... really don't know how to make.

result.php has to do 4 main tasks

prepare the input for DB query
query the DB
prepare the DB results for display
display the results

1.1) get the data from $_GET or $_POST (haven’t seen the opening <form>).
1.2) remove the unnecessary values
1.3) sanitize against SQL Injection. how you do that exactly depends on your DB system (Database Abstraction Layer, if you have one)
2) run the query
3) this again depends strongly on your DB system (results from a PDO based system differ quite much from a simple mysql_query())
4) print the values where you want them to be.

notes:
your file is neither valid XHTML nor is it ever treated as XHTML.
CSS helps you building cleaner and smaller HTML code.

scaleautostyle

thank you your answer Dormilich!!

about cleaning the file with CSS ... is what i,m doing right now... what you see at the moment is only for you helping me. this way you can see what i want to do.

Now time to jump in the CORE of the subject..

let me know what information you need to help me with the code for my result.php file.

i need to get any selection made the user in the dropdown and after that searchg the database for every matching item and after display the result.

can you gave me a hint of code that can do this. i'm using a normal mysql database nothing fancy.

this site is not for purchasing something. this is only a BIG library of everything made in 1/24 - 1/25 scale.

you can check the main page www.scale24-25.com i explain a bit more what it will do.

but for now i have to made some coding and it's whwere i have some problem

thank's in advance for your help

sebastien

Dormilich

Quote:

Originally Posted by scaleautostyle

let me know what information you need to help me with the code for my result.php file.

mainly how you want to do it, what you require, basic coding stuff.

first you need to decide, how you want to interact with your DB. from personal experience I can recommend a “Database Abstraction Layer”* (like PHP’s PDO and MySQLi classes). You should also consider using a “PHP framework”* (e.g. CodeIgniter), so you don’t have to code everything by yourself (and making DB queries is quite a common task). Once you decided about that we can talk about preparing data.

Quote:

Originally Posted by scaleautostyle

i need to get any selection made the user in the dropdown and after that searchg the database for every matching item and after display the result.

two possible routes are
1) fetching all the requested data and process that in a loop
2) fetching the matching DB keys and do fetch & display **

Quote:

Originally Posted by scaleautostyle

can you gave me a hint of code that can do this. i'm using a normal mysql database nothing fancy.

I can give you an example of code I use, but that won’t help you much.

* google search term
** this can save considerable amount of code + way more flexible

scaleautostyle

thank's for the reply Dormilich!!

i just download CodeIgniter.. that's sound great for a beginner.. but i have a problem.. it's the first it's happen and i don't know what to do.

when i open the index.php file in my browser ( using my localhost) a pop-up appear to log in my localhost. it's asking my username and password.. what's this???

also i will do what you think it will be the best for the purpose of the site i'm building. do you see the update page www.scale24-25.com/kitDB-site.php it's a bit better,

so let me know about the popup login i got and by what you want to start.

i will e a VERY GOOD student sir. promise.

thank's

sebastien

Dormilich

Quote:

Originally Posted by scaleautostyle

when i open the index.php file in my browser ( using my localhost) a pop-up appear to log in my localhost. it's asking my username and password.. what's this???

beats me why… no idea. check your server directory for a .htaccess file (this is used on apache for a login pop-up). on all posix os this is a hidden file!

Quote:

Originally Posted by scaleautostyle

also i will do what you think it will be the best for the purpose of the site i'm building. do you see the update page www.scale24-25.com/kitDB-site.php it's a bit better,

it’s not about what I think… it looks way better now :)

well, as for the HTML, get rid of the table, it’s not necessary.
and drop XHTML, if you don’t want to use the possibilities of using XML

scaleautostyle

WOU ouuu... it's working... i found the .htaccess file and delete it... now it's working..

what do you mean by rid the XHTML... i wrote my code using adobe dreamweaver selecting new PHP and open. i don't use XHTML

what do you mean by this??

Quote:

well, as for the HTML, get rid of the table, it’s not necessary.

do you mean to use CSS instead of what i use??

and now i'm ready to sstrat... hum hum.. by what we begin proffessor??

thank's

sebastien

Dormilich

Quote:

Originally Posted by scaleautostyle

what do you mean by rid the XHTML... i wrote my code using adobe dreamweaver selecting new PHP and open. i don't use XHTML

what do you mean by this??

you (Dreamweaver resp.) use a XHTML 1.0 DTD… use this instead

Expand|Select|Wrap|Line Numbers

 <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN"

        "http://www.w3.org/TR/html4/strict.dtd">

Quote:

Originally Posted by scaleautostyle

do you mean to use CSS instead of what i use??

CSS is a layout language. so if you have semantic (structural) html code, you can use CSS to make it look like you want it. (see CSS Zen Garden, always the same HTML, but each CSS brings a totally new appearance)

ex. full code

Expand|Select|Wrap|Line Numbers

             <ol id="all_params">

                <li>

                    <label for="limit_l">Intervall von:</label>

                    <input  id="limit_l" type="text" size="20" value="0" />

                </li>

                <li>

                    <label for="limit_r">Intervall bis: </label>

                    <input  id="limit_r" type="text" size="20" value="1" />

                </li>

           </ol>

Expand|Select|Wrap|Line Numbers

 ol {

    list-style-type: none;

}
 
li label {

    float: left;

    width: 10em;

    text-align: right;

    padding-right: 0.5em;

}
 
li {

    clear: both;

    padding-top: 0.5em;

}

scaleautostyle

thank's for the hint about the xhtml.. i will change all my file.

for the CSS i'm working on that already. when it will be finish i will let you know.

and now about my initial problem... the sql query neede to my search option...

by what i start ( or by what we start proffessor )

yours

sebastien

Dormilich

Quote:

Originally Posted by scaleautostyle

and now about my initial problem... the sql query neede to my search option...

by what i start ( or by what we start proffessor )

well, well, my dear pupil… ;)

this is indeed not an easy decision to make (for I’m not an SQL expert).

I guess you have to construct the SQL string according to the options on which you refine (you could in theory fetch all IDs which match each option separately and only use these that occur in every result set, but this is not good if you have huge data sets).
… looks like a reasonable way to go.

of course you should read CodeIgniter’s MySQL section (if you use CI). this should clear the matter, how you connect & query (mostly)

scaleautostyle

connecting to the database is not a problem. i don't understand how i can get the result. if you or somebody put a simple code on how to do base on my need.. ( with my field and file) it will be appreciate

ex: if someone click on the 1/24 from the scale drop down and click submit. what's the code need to return all the 1/24 found in the table kit field scale.

if he select tamiya from the manufacturer kit dropdown and honda from the reel manufacturer dropdown and then click the submit button.. what will be the code to return the info from the table kit field manufacturer_kit and manufacturer_reel.

all my code can be found in the post above. base on this file what i have to change to work. may be i have to change some variable or other things. i read, read, and RE-read. but it look like if i ask you to read an instruction in chinese. you will read 100 time but never understand. except if i gave a traduction of a small text you will be able to compare the instruction with my text and so..

i hope someone can help me i really need an answer for my problem

yours and thank's in adavance for your help

sebastien

Dormilich

Quote:

Originally Posted by scaleautostyle

connecting to the database is not a problem. i don't understand how i can get the result.

my problem here is, that I don’t know the CI mysql syntax.

Quote:

Originally Posted by scaleautostyle

ex: if someone click on the 1/24 from the scale drop down and click submit. what's the code need to return all the 1/24 found in the table kit field scale.

Expand|Select|Wrap|Line Numbers

 -- as a prepared statement

SELECT `kit_id` FROM table_name WHERE `scale` = ?

then you can use another prepared statement to fetch all the data

Expand|Select|Wrap|Line Numbers

 -- SQL

SELECT * FROM table_name WHERE `kit_id` = ?
 
# PHP

foreach ($id_array as $id)

{

  // pseudo code, use CI’s syntax instead

  $pdos->execute(array(1 => $id))

  printResult($pdos->fetch());

}

Quote:

Originally Posted by scaleautostyle

if he select tamiya from the manufacturer kit dropdown and honda from the reel manufacturer dropdown and then click the submit button.. what will be the code to return the info from the table kit field manufacturer_kit and manufacturer_reel.

Expand|Select|Wrap|Line Numbers

 -- as a prepared statement

-- get 20 results from offset

SELECT `kit_id` FROM table_name WHERE `manufacturer_kit` = ? AND `manufacturer_reel` = ? LIMIT ?,20
 
# PHP (again, mostly pseudo code)

$pdos->execute(array(1 => "tamiya", 2 => "honda", 3 => 0));

$id_array = $pdos->fetchAll();

the output code is the same as above

scaleautostyle

still get the #$#%$% problem this is the link of the page

http://www.scale24-25.com/kitDB-site.php

for this page NO PROBLEM.. here it is... when you clic on the go button it supposed to return what the user select in the field. ex: you choose 1/24 and honda. a query will search the database under the column scale to find every 1/24 and under the constructor name to find all honda and them display the result.

this query have to be made in a separate files name kitDB-result.php.

but i REALY don't know what the query need to be.

if you can gave me an exemple of the query using my data it will be appreciate

yours

sebastien

PS: use this.

database name: sebastien
username: SAS
password: resinkit

sale column = scale
kit manufacturer = kit_manufacturer
engine = engine

thanks in advance

Frinavale

Did you try the SQL statement that Dormilich suggested?
It looks "right" to me too, mind you I'm not a SQL expert...

Whenever I get stuck on SQL stuff I turn to my trusty database book or the w3cschools site. The W3CSchools site has examples on the Select Statement and the Where Clause that may help you get an idea of what you need to do....

RedSon

Quote:

SELECT `kit_id` FROM table_name WHERE `manufacturer_kit` = ? AND `manufacturer_reel` = ?

This is a simple select statement. It should work no problem. Be aware however that you need to replace the ? with actual data. If that select gets the data you want then you can start to LIMIT the output and order it and do all sorts of fancy things. Just get the basic SQL down first.

The only problem is that you are selecting only one field of data "kit_id" if you want everything from the whole table "table_name" you would change kit_id to *.

scaleautostyle

Quote:

Originally Posted by RedSon

SELECT `kit_id` FROM table_name WHERE `manufacturer_kit` = ? AND `manufacturer_reel` = ?

ok but how I can replace the ? by the selection of the user....

thanks in advance

sebastien

TheServant

Expand|Select|Wrap|Line Numbers

 mysql_query( "

    SELECT kit_id

    FROM table_name

    WHERE manufacturer_kit='$manufacturer_kit' AND manufacturer_reel='$manufacturer_reel'

    LIMIT 1

" );

Obviously you will need to define your $manufacturer_kit and $manufacturer_reel variables. I made a string using " so that php variable can be included, and then I wrapped the variables in ' so that MySQL is happy. You could also have a multipart string, but that is easier. I also added a LIMIT 1, so that only one record is returned, and it stops searching after it gets that one. Look it up if you haven't used it before.

Dormilich

Quote:

Originally Posted by RedSon

This is a simple select statement. It should work no problem. Be aware however that you need to replace the ? with actual data. […]

The only problem is that you are selecting only one field of data "kit_id" if you want everything from the whole table "table_name" you would change kit_id to *.

You’ve got the intention wrong. this is not a simple SELECT statement to display the results, it is a SELECT statement for use as Prepared Statement. the "?" will be replaced in the execute() call. further this statement only fetches the kit_id, because for the result display, there will be used another Prepared Statement, which uses the selected IDs to get the complete data.

Dormilich

Quote:

Originally Posted by scaleautostyle

ok but how I can replace the ? by the selection of the user....

Expand|Select|Wrap|Line Numbers

$PDOStatement->execute(array(1 => "tamiya", 2 => "honda"));

TheServant

Quote:

Originally Posted by Dormilich

Expand|Select|Wrap|Line Numbers

$PDOStatement->execute(array(1 => "tamiya", 2 => "honda"));

lol, pays to read the post. Didn't realize it was CI - Ignore my previous post!

Dormilich

well, it wasn’t even CI, just some plain PDO.

display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

re: display the result of search query

Similar PHP bytes

/bytes/development

/bytes/it

/bytes/about