PHP MySQL
Moderator: General Moderators
Re: PHP MySQL
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.
-
windywindle
- Forum Newbie
- Posts: 3
- Joined: Wed Aug 28, 2013 6:02 am
Re: PHP MySQL
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
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
Last edited by requinix on Wed Aug 28, 2013 12:32 pm, edited 1 time in total.
Reason: using url tag, uploading image
Reason: using url tag, uploading image
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: PHP MySQL
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.
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.
(#10850)
-
windywindle
- Forum Newbie
- Posts: 3
- Joined: Wed Aug 28, 2013 6:02 am
Re: PHP MySQL
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
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: PHP MySQL
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.
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.
(#10850)
Re: PHP MySQL
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.
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));
}- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: PHP MySQL
See the manual for the correct function name.Celauran wrote:The error message spells it out for you. You're calling mysql_connect_error(). There's no such function.
(#10850)
Re: PHP MySQL
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.