MySQL help? Will pay.

[yuppicide]

Does anyone want to make some extra cash and help me with a MySQL database and some website coding?

When our new catalog comes out I'm changing the look of our website. I want a page on our website where people will to be able to search through our whole
catalog.

Here's the fields I want in the database:

Item Number
Colors
Description
Page

.. Maybe one more catagory, Material.

Item Number examples are: 1120G-6153, 3134-7190, 9601-2000, etc.
Color could be more than one if the item comes in more than one color. Color examples: Multi, Black/White, Black/Tan, Red, Orange, etc.
Description would be what the item is. Examples are skirt, pareo, kaftan, tunic, dress, etc.
Page would be the page number the item appears on.
Material examples would be cotton, rayon, etc.

I want a search page on the website where people can search for items and view pictures.

Here's ways people would be able to search:

1 - For example if someone wanted to pull up just one specific item they can enter it under Item Number.. so typing in 9601-2000 would pull up pictures of all
that item in every color.

2 - If someone wanted to see all 9601's (which might take awhile and pull up a few hundred items) it will pull up pictures of every 9601 in every color
showing show only so many results per page.

3 - Maybe someone wants to see everything we sell that comes in brown. They could do that also.

4 - Maybe they just want to see all skirts. They could do that.

5 - Maybe they want to see everything from page 26 of our catalog. They could do that.

6 - If someone wanted to see everything we sell made out of cotton they could do that also.

I just want someone to start me off. I'll supply maybe 20 or 30 items with all the information including pictures for the database. I'll have a free database
to test everything out with. Once we get that working I'll change it all over to my MySQL database on Yahoo! Small Business where we host our website. The
reason I am not using our Yahoo! MySQL database right now is because our Network Administrator has the password to our Yahoo! account and he has to enable
the database. We have to pay him for his time, so we're not doing it right now, but if I can show them a working demo of what is to come, I am sure that
database will be turned on the same day. I just want to get things started because there will probably be over 400 items to add to the database and I can't
pay someone for their time to do 400+ items. I just want help setting up, making the search page, and showing me how to add more entries to the database and
then I'll take it from there.

[curtiss]

I don't know how I missed this post, but I'd be willing to help you out. Let me know what you need, and I'll take a look at it early next week (going to be out of town this weekend, otherwise I'd work on it then). It shouldn't take more than a couple of hours to get something like that going.

[yuppicide]

Thanks.

I notice Yahoo! Small Business is using an old version of MySQL and PHP.. version 4 of each I think. They also have PHP MyAdmin if I need and PHP Nuke.

All of them need to be enabled by our network consultant, which is why I was kind of hoping to get a test database running on a free site first. But if it would be better just getting them to enable the features I need on Yahoo! first then I'll do that.

[yuppicide]

Here's my current progress.. I changed my catalog PHP page to reflect the search terms. The items up top are primary keys (is that what you call them in MySQL?). Those are the things you will be able to search by.

http://www.peppermint-bay.com/betatest/cat.php

[curtiss]

Okay. First of all, you need to develop your form correctly. Here are some tips, which I will use when I show you the PHP code you will need:

1. Your input names should never have spaces in them. For instance, "Item Number" should actually be named something like "ItemNumber" or "item_number" or something similar.
2. Always assign an ID to your form elements. When using PHP, you can actually leave out the "name" altogether, and just use the ID. With ASP, you are required to use the name. With JavaScript, the name is the more conventional way of doing things, but you can get everything accomplished using the ID. ID's should always be unique. No item can ever have exactly the same ID as another item.
3. Make sure you assign a name and ID to your "Search" button.
4. Wrap each of your form element labels in <label> tags. For instance, rather than using:
   

   <b>Item Number:</b>

   you should use

   <label>Item Number:</label>

   You can then use CSS to make your labels bold.
5. When using select elements, radio buttons or checkboxes with PHP, you will need to add opening and closing square brackets to the names of your form elements. In other words, the name of your second form element should be "color[]" instead of just "color". This tells PHP that this might be an array.
6. In order to save hassles, make sure that your SQL table columns have the same names as your form elements. In other words, if you have a column in your table called "ItemNum", then your matching form element should be called "ItemNum".

Now, your PHP should look something like:

PHP Code:

if($_POST[btn_search] == "Search") {
    $mySearchItems = array("item_number","color","description","page");
    $sql_array = array();
    $i = 0;
    foreach($mySearchItems as $term) {
        if(!empty($_POST[$term])) {
            if(is_array($_POST[$term])) {
                $_POST[$term] = join($_POST[$term]," OR `$term` = ";
            }
            $sql_array[$i] = " (`$term` = '$_POST[$term]')";
            $i++;
        }
    }
    $sql = "SELECT * FROM `CatalogItems` WHERE".join($sql_array, " AND")." ORDER BY `item_number` ASC";
    $sql = mysql_query($sql, $link);
    while($rs = mysql_fetch_array($sql)) {
        $catalog_echo .= "
        <div class='result_line'>";
        foreach($mySearchItems as $term) {
            $catalog_echo .= "
            <div class='result_item'>$rs[$term]</div>";
        }
        $catalog_echo .= "
        </div>";
    }
} 


That will get you started on the PHP end of it. The SQL end of it is pretty simple once you find a place to set up a development database.

There is only one thing left to add to the PHP code, which would be to set up partial matching on the item number. It's very simple to do, but we'll work on that once we make sure that the PHP I showed you above will work for you.

[yuppicide]

Thanks. Have made changes you said. This Friday when I get paid I'll pick up my own database somewhere to use and bill the company when it's all done. I'll update my progress on Monday.

[yuppicide]

Okay. I've created a free database. I finally found one that is somewhat speedy, actually sent me a confirmation email, and didn't require me giving any credit card information.

I just made up a name when I signed up. I created a database and used the default name it gave me:

joerol5?test

It asked me to pick something from a drop down box.. it was on Collation so I left that.

Now it's asking me to create a table and number of fields. I am assuming I want 1 table with 4 fields. One for ItemNumber, one for Color, one for Description, and one for PageNumber.

Do I set each one to Primary Key? I think I want ItemNumber and color indexed.. should I index any others? It has me select either primary, indexed, unique, or none.

[curtiss]

Are you using PHPMyAdmin to administer the database, or do they have some other tool?

You should set item number to primary key. Do not set any others to primary key, as none of the other fields will be unique. The primary key has to be unique. In the case where none of your columns will be totally unique, then you would add a unique index (usually an auto_increment number of some sort) as your ID.

I would go ahead an index all 4 columns, as you will be searching the database from each of the 4 columns. Do not set any of them to unique, as the ItemNumber is the only column that will contain completely unique entries.

[yuppicide]

>> Are you using PHPMyAdmin to administer the database, or do they have some other tool?

PHPMyAdmin.

>> You should set item number to primary key. Do not set any others to primary key, as none >> of the other fields will be unique. The primary key has to be unique. In the case where
>> none of your columns will be totally unique, then you would add a unique index (usually an >> auto_increment number of some sort) as your ID.
>> I would go ahead an index all 4 columns, as you will be searching the database from each >> of the 4 columns. Do not set any of them to unique, as the ItemNumber is the only column >> that will contain completely unique entries.

Okay. I don't think any of the columns will be unique. The reason I say that is for example one item 9601-2000 has maybe 6 or 7 colors.. so won't that be a different line on my table?

9601-2000 Black
9601-2000 Brown
9601-2000 Coral
9601-2000 Etc, etc.

So I'll add one more field called ID and make that unique?

When I go in it only let me select one radio button.. so I can only select Primary Key, Index, Unique or none.

[yuppicide]

I tried fooling around with a program called MySQL PHP Generator. Just wanted to see what it can do, but it won't let me login to my database. Maybe the free service I am using won't allow it?

It asked for host, port, username, password, and database name. It comes back with "Access denied for user and lists my username @ my ip address.