[RESOLVED] I need help with a complicated script.

Scooby_Doo

I am building a portfolio for my site and when I add another design, I would like to have a drop down with all the available categories. So when I choose a certain category it automaticly puts that design in that category.

Then in the navigation, people can choose a category and it takes them to a page with all the designs of that category.

greensweater

Are you using a database on the backend? If so, create a table for categories and a table for items (or designs). Give each item record a category id.

Populate the drop down using SELECT id,title FROM categories.

You could fire an onchange javascript event on the dropdown, to post back the dropdown's enclosing form data.

Near the beginning of the script check for $POST['categories'] and change your HTML output accordingly; you could loop through all the entries in
"SELECT title,imageurl,content FROM items WHERE categoryid=".mysql_real_escape_string($POST['categories']); if the page is a postback.

bpat1434

That's fantastic. I'm glad to help. What's your question?

Scooby_Doo

bpat1434 wrote:
That's fantastic. I'm glad to help. What's your question?

Here is a video of me explaining what I have and where my problem lies.
http://www.youtube.com/watch?v=1PNb87Rve24

I've designed a form that inserts all the designs and a form that adds a category to the categories table.

I can also select all the different categories and make them navigation links

I am hosting this on my computer so If I need to host it on my domain please tell me.

Code:

<?php
$conn = mysql_connect(localhost, root, "") 
or die
    ('Error connecting to mysql');
mysql_select_db(sites);						

$result = mysql_query("SELECT * FROM categories");
while($row = mysql_fetch_assoc($result))
{
    echo "<a href='/category.php?id={$row['id']}'>";
	echo $row['name'];
	echo "</a>";
	echo "<br>";
}

mysql_close($conn);
?>

But my problem is if I click on one of the links it takes me to the next page, but doesn't list a the designs in the designs table.

I have added a design to a table called "space". (I made a starfield background)

Code:

<?php
$conn = mysql_connect(localhost, root, "") 
or die
    ('Error connecting to mysql');
mysql_select_db(sites);						

$catid = mysql_real_escape_string($_GET['id']);
$result = mysql_query("SELECT * FROM layouts
WHERE categoryid={$catid}");

while($row = mysql_fetch_array($result))
  {
  echo $row['title'] . " " . $row['bgimg'];
  echo "<br />";
  }

?>

bpat1434

[offtopic]I have to say, I love southern accents 🙂[/offtopic]

Okay, nice video.

Now, your table structure should probably be more like two tables: One for the categories, and one for the design. So your current DB structure (with everything in one table) will work, except that when you misspell something, or want to change "php_builder" to "PHPBuilder", you have to edit every row, instead of just one row 😉

But anyway... that's a different issue. Let's get your stuff running.

Okay, so first test:
Using the coding above (i.e. the original query), do me a favor and go to this URL:
/category.php?id=Space

I'm just going to say: it will work.

Why does it work?
It works because you stated that the WHERE clause of your SQL has to be manually set to "Space"; however, in your URL you use the integer "id" of the rows instead of the string "categoryid". So if you changed your first file's php code from:

echo "<a href='/category.php?id={$row['id']}'>";

echo "<a href='/category.php?id={$row['categoryid']}'>";

Your code should then work.

To split your tables into two, you'd have one table category and the other called design. For example, category would be the following:

id  |  title
----+------------
 1  |  Space
 2  |  PHPBuilder
 3  |  Test

The table design would be something like:

id |  categoryid  |    title    |  bgcolor  |        bgimage         |  bgrepeat  |  backcolor  |
---+--------------+-------------+-----------+------------------------+------------+-------------+
 1 |  1           |  Starfield  |  000000   |  bgimagestarfield.jpg  |  YES       |  FFFFFF     |
 2 |  1           |  Video      |  000000   |  bgimagestarfield.jpg  |  YES       |  FFFFFF     |

Now, your first script would query the category table for all categories, and use your original code (so $row['id'] instead of $row['categoryid'] like I suggested earlier).

The second script would not need to be changed, except to query the design table.

Hope that helps you along, and gets your problem solved in one way or another.

Scooby_Doo

Thank you so much, I'm about 75% there.
Here's a video that explains my problem

http://www.youtube.com/watch?v=cKGM8pHD-c0

Heres the code to the navigation page.

<?php
$conn = mysql_connect(localhost, root, "") 
or die
    ('Error connecting to mysql');
mysql_select_db(sites);						

$result = mysql_query("SELECT * FROM layouts, lcategories");
while($row = mysql_fetch_assoc($result))
{
    echo "<a href='/category.php?categoryid={$row['categoryid']}'>";
	echo $row['name'];
	echo "</a>";
	echo "<br>";
}

mysql_close($conn);
?>

The other page works but just in case
Code:

<?php
$conn = mysql_connect(localhost, root, "") 
or die
    ('Error connecting to mysql');
mysql_select_db(sites);						

$catid = mysql_real_escape_string($_GET['categoryid']);
$result = mysql_query("SELECT * FROM layouts
WHERE categoryid={$catid}");

while($row = mysql_fetch_assoc($result))
  {
  echo $row['title'] . " " . $row['bgimg'];
  echo "<br />";
  }

?>

bpat1434

Your SQL join is wrong. When you list multiple tables in your SQL query (like you have now) it creates a JOIN condition. If left the way you have it, it creates what's called a Cartesian Join. If you can remember from your math days, it's essentially the same as a Cartesian Product. A new product (or table in this case) will be created where all possible combinations of the join are used. So for everything in the layouts table (2 rows) will be matched with each row of the lcategories table (4 rows) to give you 8 results.

Now, to fix this, we have to decide which data we really want. There are two ways to do this. We can limit our result to only what's in the layouts table (i.e. only those categories which have things in them) or we can show all categories, and show there's nothing in there after they follow the link.

Limiting to What's In the Layouts Table
This query will use only as many rows as are in the layouts table. So only those lcategories that have items associated with them in the layouts table will be shown. So in our case, only the lcategories "Space" and "Test" will be shown. We are going to join the lcategories table onto the layouts table. We will specify that it is a LEFT join using the SQL keyword "LEFT". Now, what this means is that the table on the left of the ON condition will be the basis to which the other table joins in. So by saying LEFT JOIN lcategories ON layouts.categoryid=lcategories.id we will limit our result set to only 2 items. The full query:

SELECT l.*, lc.*
FROM `layouts` AS l
LEFT JOIN `lcategories` AS lc
   ON l.`categoryid` = lc.`id`
ORDER BY lc.`name`

Show All Categories
To show all the categories, we can do one of two things: (1) Make the join a "RIGHT" join instead of left above, or (2) rework the query so that it's a left join. Reworking the query takes little effort, so I'll show you both:
LEFT JOIN

SELECT l.*, lc.*
FROM `lcategories` AS lc
LEFT JOIN `layouts` AS l
   ON lc.`id` = l.`categoryid`
ORDER BY lc.`name`

RIGHT JOIN

SELECT l.*, lc.*
FROM `layouts` AS l
RIGHT JOIN `lcategories` AS lc
   ON l.`categoryid` = lc.`id`
ORDER BY lc.`name`

So that should move you even more toward your goal. Let me know if you need any more help.

Scooby_Doo

It works now!!! thank you so much. I can't thank you enough.

By the way.what php book would you recomend for a php newbie. and if you used a book(s) which one(s). Again thank you.

EDIT: What would I do to get all that designs info and put it on the page.

Like if a person clicked on a design it would get all that designs information.

I. E. (The title, background image, and ect.)

bpat1434

I didn't really read any books to start me with php. Just look at my first maybe 1000 posts around here and you'll kinda see where I learned. To give you a concise version of how I got started:

A friend asked me if I knew any php, and I fibbed and said yes. I knew some basic ASP and figured that ASP couldn't be that different from php. Well, after debugging someone else's script that my friend was using, I got interested. I started taking that and using it for personal websites and sites I designed.

I was "contracted" to work on my local fire departments website and decided I'd use them as a guinea pig to test my new-found php skills. I tried to create a CMS type of system for them. In hindsight, I should have just done a static site (lost their "contract" to someone else).

Anyway, from there I just lurked the forums here, and picked up some basic PHP here. I started playing with MySQL and eventually learned a lot. The best way I learned was to take people's problems from here, recreate them, and then try to figure them out. Once I got good at it, I just honed in on what areas I was weak at (MySQL, image manipulation, etc.).

Later on I started creating my own functions and testing my ability with php by writing code I'd never use, but thought it would be challenging. One example is the vertical / horizontal looper I posted on here a few times.

From there, I just picked up certain books that appealed to me. I have Professional PHP5 published by Wrox as well as Programming PHP published by O'Reilly as well as PHP Security by Chris Shifflet and Learning SQL published by O'Reilly. Those are a good set of books I feel to read once you know your way around the php.

I never learned php from books (good thing or bad thing?) but when I read php books, I understand 50% of it. The other 50% is what they're trying to teach you 😉

To get all the design information, you'd have to change that last query's output to links (if you want) to query the database again only picking up that row's information (so "SELECT * FROM layouts WHERE id='{$_GET['layout']}'") and then use whatever you have in the database to display on your page.

EDIT: Okay, so out of curiosity, are you from the southern US? Listening to your voice sounds like a southern accent, and my girlfriend (who is from Oklahoma) disagrees with me.

Scooby_Doo

Yes, I'm from southern US. I've lived here all my life. Back on subject I have it working perfectly. Thank you so much.

I have only gotten one book for web design and that was for css. I learned html from a girls site called horseland designing layouts for people. :quiet:

But in my opinion I think that it requires both the forums and a book, because the forums give you more realistic comparisons while as books or in my case W3Schools only give to the bare minimum that basically says well, "Now I've given you what you need figure it out yourself".

But like I said before I've only gotten one book so I might not be correct.

bpat1434

I've always been an advocate of Learn By Doing. What better way to teach yourself than to put yourself right in the thick of the problem. I learned my way around my home-town by just driving to get lost in back-roads, and then finding my way back out. Of course that was before gas was $2.00/gal.

If you're looking for a good book, I'd probably have to suggest any of the O'Reilly books. I'm not sure how new you are, or what you understand; so you'll have to decide. Best way I buy books is to go to Barnes 'n Noble (or any book-store) and just go to the computer section, find the web development section, and start looking at the books. Read the first few pages, read the back. Then I make a decision based on what I see and how straightforward it is.

Glad to hear you got it working 100%. Don't forget to mark this as resolved. Any other questions, feel free to ask!!

Oh, and to teach myself HTML, I got a book on HTML (I can't find it anymore) and read that. But the CSS I learned mainly through a site called csscreator.com and PHP I learned here. I've now read books on almost all topics; however, I learned by doing, then went back to read up on other tactics.

Scooby_Doo

I'm sorry to bother you, I guess I should have checked this first but o well.
I don't know how to write this so I made a video.

http://www.youtube.com/watch?v=yeq5rPf48Lc

<?php
$conn = mysql_connect(localhost, root, "") 
or die
    ('Error connecting to mysql');
mysql_select_db(sites);						

$result = mysql_query("
SELECT l.*, lc.*
FROM `layouts` AS l
LEFT JOIN `lcategories` AS lc
   ON l.`categoryid` = lc.`id`
ORDER BY lc.`name`");
while($row = mysql_fetch_assoc($result))
{
    echo "<a href='/category.php?categoryid={$row['categoryid']}'>";
	echo "<font size=\"36\">";
	echo $row['name'];
	echo "</font>";
	echo "</a>";
	echo "<br>";
}

mysql_close($conn);
?>

bpat1434

No problem to bother again. I actually enjoy this 😉

Go back and read the last half of my previous post where I talk about what data limits the results.

Right now, you will always have as many results as are in the "layouts" table. Along with that, you're actually selecting all columns of each table. So if each table has a column "name" you can get weird results. To fix this, only pull the column names you need at that time. So if you only want the column "title" from the lcategories table, use "lc.title" instead of "l.*". So for example your query could be:

SELECT l.name, l.id, lc.title
FROM `layouts`
LEFT JOIN `lcategories` AS lc
   ON l.`categoryid` = lc.`id`
ORDER BY l.`name`

Hope that helps.

Scooby_Doo

Ok, that makes me feel better, I'm sorry it won't work, most of it wasn't your fault.

Here's another video of the problem.
http://www.youtube.com/watch?v=9s_KZZ7yAsU