Page 1 of 1

Re: PHP MySQL

Posted: Wed Aug 28, 2013 6:29 am
by Celauran
How are you keeping track of what page they're on? Get parameters (ie. yoursite.com/?catID=1&subcatID=2)? Those are what you'll want to pass to your query. Looks like table 1 is your subcategories table and table 2 is your businesses table, with SubCategory as a foreign key. You'll want join these tables on that value, then. I can't really come up with a query for you as I don't know much about the structure of the respective tables and it's not clear what you'd want from each.

Re: PHP MySQL

Posted: Wed Aug 28, 2013 12:06 pm
by windywindle
So the way this goes.... a user comes to the site. Clicks on a category from the main page. Then is directed to another page where they are presented with a set of sub categories for that category. Upon selecting a sub category they are presented with a list of businesses that correspond to that category.

The question is how can i make it so i dont have to create loads of p[ages for each sub category but use ionly one..... and how do i go about doing this in SQL.

There is an image below of the categories and sub cats sql db.

http://webmail.webstudioone.co.uk/publi ... PC5g%3d%3d

Re: PHP MySQL

Posted: Wed Aug 28, 2013 1:16 pm
by Christopher
Seem like you have this a little backwards for you relation. I would do:

Category Table 1:
[*]id - (Auto Generated)
[*]Category
[*]LinkPage
etc...

Sub-Category Table 2
[*]id
[*]CategoryId
[*]SubCategory
[*]Business Name
etc...

The to find all the sub-categories in a category you would use this SQL:

"SELECT * FROM subcategory WHERE CategoryId=$categoryId"

You could also do this all in one table using the sub-category table and have the top level categories have a CategoryId of zero.

Re: PHP MySQL

Posted: Wed Aug 28, 2013 1:38 pm
by windywindle
That's great...... So the question is how do I get it into a php code where I just use a single page to show businesses in the subcategory. IE: yoursite.com/bizlist.php/?catID=1&subcatID=2

Re: PHP MySQL

Posted: Wed Aug 28, 2013 11:07 pm
by Christopher
First you want to use either the PDO or mysqli extensions. If you look in the PHP manual there are examples of fetching rows using each extension.

I would recommend creating a separate class to fetch the records. Pass a PDO or mysqli object to the constructor when creating an object. That class is your Model. Create a method named something like findSubCategories($categoryId). that returns an array of rows from the database.

Generate your display by foreach()'ing through the array or rows and generating HTML.

Re: PHP MySQL

Posted: Thu Aug 29, 2013 6:36 am
by Celauran
1. Stop using mysql_. It has been deprecated and is going to be removed from future versions of PHP. Go read up on PDO.
2. Stop using SELECT *. Specify which columns you want and select only those. This will cut down on the amount of data being sent and, more importantly, will raise errors should columns be removed or renamed.
3. Don't use SQL queries inside a loop. Think about what you want to accomplish and write a better query.

Now, onto the matter at hand. You're setting the value of all the checkboxes to 1. You're then fetching all rows with category 1, looping through them, and then setting the value of highlighted to 1, possibly multiple times.

What you want to do is set the value in the checkbox field to be the product's ID. Rows not checked will not be part of the $_POST['ids'] array, so you simply gather up all the values of $_POST['ids'] and run your update query against those values.

Code: Select all

<?php
    
$sql = new PDO('mysql:host=localhost;dbname=whatever;', 'username', 'password');

$query = "UPDATE products SET highlighted = 1 WHERE id = :id";
$stmt = $sql->prepare($query);
foreach ($_POST['ids'] as $id) {
    $stmt->execute(array(':id' => $id));
}

Re: PHP MySQL

Posted: Tue Sep 10, 2013 12:24 pm
by Christopher
Celauran wrote:The error message spells it out for you. You're calling mysql_connect_error(). There's no such function.
See the manual for the correct function name.

Re: PHP MySQL

Posted: Fri Sep 13, 2013 6:00 am
by Celauran
You really shouldn't be using the mysql_ functions. They have been deprecated and will be removed from the language. Do yourself a favour and take a look at PDO. Also, I see you're saving passwords as plain text (and using a text input instead of a password input). This is beyond bad practice. User passwords need to be hashed.