Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

total php/sql noob here

2:24 pm on May 11, 2011 (gmt 0)

New User

joined:May 11, 2011
posts: 2
votes: 0

i am attempting to create a site which has a search page linking to a databse, but have a few problems before i have even started.
The site will contain a list of people, searchable by various factors, and also searchable by availability dates. Is this possible using sql/php and if so, any advice on how i should procede?
I am having problems knowing how to format the database to make it searchable by more than 1 data type, and also how to store and search availability dates.
This is probably too vague i know, but any help apreciated.
4:20 pm on May 11, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
votes: 0

Welcome aboard Dead_One, we don't know what you know. So

Start here [w3schools.com] with the basic PHP/mySQL tutorials.

The mySQL manual [dev.mysql.com] is invaluable.

Some seeds for a newbie . . .

- Don't try to do anything in PHP without looking into mySQL functions first. Too often I see programmers struggling to do in programming what is easily down in mySQL, for example, date math or ordering and sorting.

- Learn about cleansing input, every potential user input - even a query string - is a potential point of entry for a hacker. Accept only what you want and throw everything else away; what you do accept, filter it ruthlessly. Too often programmers stumble along with the intent of learning about this later - make it an integral part of your learning process.

- Understand data types, use them appropriately, understanding that selecting anything numeric will always be faster than textual. For example, instead of storing "category" as a plain varchar field for every record, think forward. What if you want to change the name of the category? What if you want that product in several categories? The "short answer" is you have three tables, a category table identified by record id, a products table with products identified by record id, and a table to tie them together, allowing any given product to be connected to multiple categories.

1234|Widget|This is a widget, it's nothing really, just a thingie.
4567|Thingamabob|This is a more specific type of nothing, different than a widget.

15|too good for you


- Don't hard code, think dynamic, think of various tasks as "black boxes" when you code. Linear programming goes something like this:

- initialize the script
- connect with the database
- do some stuff
- output to the browser

Too often you will see many .php files with the same code in the files repetitively. That should all be pulled out and used as a function in an include script. Recycle, reuse, compact, economize.

On "decent" example of what I'm talking about is actually relevant to this forum and your question. :-) When you say "different data types" I am guessing you mean "different fields." Take for example, using the previous data structure, if you build a form that searches by keyword and category. First we would build a select list of categories so it uses their numeric values:

<select name="cat" id="cat">
<option value="">select category</option>
<option value="12">good</option>
<option value="13">better</option>
<option value="14">best</option>
<option value="15">too good for you</option>

Then we have a keyword search on the product title **or** content.

<input type="text" name="kwd" id="kwd" value="">

When this is submitted, we would put those in a mysql select statement **only** if there are values input from the form. This would mean if someone just hist "submit" without entering anything, it would display all records.

$where = null;

if (isset($_POST['cat']) and is_numeric($_POST['cat']) and ($_POST['cat'] > 0)) {
$where = " products.id=product_categories.prod_id and product_categories.cat_id=" . $_POST['cat'];

if (isset($_POST['kwd']) and ! empty($_POST['kwd'])) {
// Letters, spaces, numbers only, case insensitive
$_POST['kwd'] = preg_replace('/[^a-z\d\s]+/','',$_POST['kwd']);
if ($where) { $where .= ' and'; }
$where .= " (products.name like '%" . $_POST['kwd'] . "%'" or products.description like '%" . $_POST['kwd'] . "%')";

$select = "select * from products";
if (isset($_POST['cat']) and is_numeric($_POST['cat']) and ($_POST['cat'] > 0)) {
$select .= ',product_categories';
if ($where) { $select .= " where $where"; }

This could give you multiple possible selects.

select * from products

select * from products,product_categories where products.id=product_categories.prod_id and product_categories.cat_id=1234

select * from products,product_categories where products.id=product_categories.prod_id and product_categories.cat_id=1234 and products.name like 'widg'

select * from products where products.name like 'widg'

This allows your searching and selecting proces to grow as your program's needs do, rather than hard coding selects in your programs.

The bit where I examine the category and make sure it's numeric,

if (isset($_POST['cat']) and is_numeric($_POST['cat']) and ($_POST['cat'] > 0)) {

is an example of **one way** to cleanse the data. We are only accepting numeric values there greater than zero, any other input will be ignored.

also how to store and search availability dates.

Post is already too long, but will lead you to the right place to look: Use select lists for dates (so the user can't error in input) and look into the mySQL date and datetime formats. If you don't store your dates in these formats, you lose all the date and time functions - including date math - inherent in mySQL.

The previous are not perfect examples, or even the best solutions, but they are intended to get you thinking about programming dynamically before you jump into your first script. Good luck on your journey . . .
7:59 am on May 12, 2011 (gmt 0)

New User

joined:May 11, 2011
posts: 2
votes: 0

thanks for the in depth reply:-)
What i am trying to do is to create a website for a martial arts fight team, to show availability of the fighters.
I need to get it so that someone can access the search form, then search for an available fighter by sex, weight, class and available on a specific date. I also need to store other data on the fighter i.e. fight record, fight history, maybe some background on them, and poss. a picture or 2.
Does this seem like something i can do in php/mysql?
4:41 pm on May 12, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
votes: 0

Absolutely, any server side language + SQL. But you have to walk before you can run. :-)
6:02 pm on June 13, 2011 (gmt 0)

New User

joined:June 13, 2011
votes: 0

I recommend you a very good sql tutorial named: Learning sql queries basics with sql exercises [squidoo.com], that will guide you through sql basics with examples and some exercises to test yourself. There is a very good explanation of everything.