homepage Welcome to WebmasterWorld Guest from 54.161.191.254
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
total php/sql noob here
Dead_One




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

Hi,
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.
thanks

 

rocknbil




msg:4310826
 4:20 pm on May 11, 2011 (gmt 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.

products
id|name|description
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.

categories
id|title
12|good
13|better
14|best
15|too good for you

product_categories
id|cat_id|prod_id
12|13|1234
12|14|1234
12|15|1234
12|12|4567
12|15|4567

- 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>
</select>

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 . . .

Dead_One




msg:4311212
 7:59 am on May 12, 2011 (gmt 0)

Hi,
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?
thanks

rocknbil




msg:4311411
 4:41 pm on May 12, 2011 (gmt 0)

Absolutely, any server side language + SQL. But you have to walk before you can run. :-)

speaker226




msg:4325587
 6:02 pm on Jun 13, 2011 (gmt 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved