To anyone reading this that uses MySQL and Php, you should at this moment go and get a Visual Query Builder right now! I'm starting to believe that the QuB really does simplify your life a lot on MySQl queries. Just look at this query I built with QuB:-
"
SELECT multiply_test.multiply_date, users_info.*, multiply_test.multiply_id, multiply_test.multiply_1, multiply_test.multiply_2, multiply_test.multi_result, max(bids.bids_item_price) AS max_bids_item_price_1
FROM ((multiply_test
INNER JOIN bids ON bids.bids_item_id=multiply_test.multiply_1)
INNER JOIN users_info ON users_info.id=multiply_test.multiply_1)
GROUP BY multiply_test.multiply_date, multiply_test.multiply_id, multiply_test.multiply_1, multiply_test.multiply_2, multiply_test.multi_result, users_info.id, users_info.upl_name, users_info.upl_file, users_info.loc, users_info.users_total_ads, users_info.users_total_earn, users_info.users_total_paid, users_info.users_total_unpaid
ORDER BY multiply_test.multiply_date DESC, max_bids_item_price_1 DESC
"
Seriously hard work there to figure out if you're using hand coding....
Showing posts with label MySQL. Show all posts
Showing posts with label MySQL. Show all posts
Thursday, July 9, 2009
Tuesday, July 7, 2009
Search Engine Function for Websites - 7th July
Today is the birthday for my friend: James! Happy Birthday and don't merepek too much. I'm sure while he's reading this post, he will sure be saying something like 'celaka'...haha...I was the 7th person, so, that makes it: 7th July (7) person number seven...haha..all sevens...
Anyways, I've found out how to make your own search engine for websites. Application includes: keyword search, article search, people search, and item search. It is based on MySQL and also Php, so anyone doing both can follow this method. As for the others, I would like to express my sorrow as I can't help you. So, on to the method.
We have two pages:- search_form.php and search_result.php
search_form.php is where people key-in the search term and press 'SEARCH' while search_result.php is where the results of the search is located. The key point of the method is using the form to 'GET' the data as URL Parameters and sending it over to the Recordset in search_result.php to display results.
Procedures:-
1> Start by creating a database for the table that you want to be searched from.
In other words, it can be any table, as long as there is one column for the Incremental ID and the search term.
2> Then in search_form.php, create a form to get the data from the user. Dropdown lists are also good idea for extra search. This can be done in Dreamweaver through the use of Insert -> Form, then the text-fields etc, but make sure to set the method to GET and action to search_result.php
3> In search_result.php, create a recordset and use the QuB. In the table that shows the table structure, make sure to select the column that you want the search function to check only. Under 'condition' (below), click the right-hand '...' box. In the ensuing window, set condition to "contains", run-time value as "0" and default value as the text-field 'id' created at search_form.php earlier.
4> Go to "Query" menu at top left corner and "save" then "close". Click 'Advanced to see the query something like below:-
SELECT multiply_test.multiply_id (row 1)
FROM multiply_test
WHERE multiply_test.multiply_id LIKE %KTColParam1% (row 3)
change row(1) to " SELECT * " to properly reflect all fields.
and change the %ColParam1% properties of run-time value to $_GET['x'] ,where x is search_term id as mentioned earlier.
5> Walah..the search function is done! You can use multiple search terms by manipulating the MySQL query by adding 'AND' and 'OR' with other columns at row 3.
Hope people reading it can understand it and if not, again, email me!
Anyways, I've found out how to make your own search engine for websites. Application includes: keyword search, article search, people search, and item search. It is based on MySQL and also Php, so anyone doing both can follow this method. As for the others, I would like to express my sorrow as I can't help you. So, on to the method.
We have two pages:- search_form.php and search_result.php
search_form.php is where people key-in the search term and press 'SEARCH' while search_result.php is where the results of the search is located. The key point of the method is using the form to 'GET' the data as URL Parameters and sending it over to the Recordset in search_result.php to display results.
Procedures:-
1> Start by creating a database for the table that you want to be searched from.
In other words, it can be any table, as long as there is one column for the Incremental ID and the search term.
2> Then in search_form.php, create a form to get the data from the user. Dropdown lists are also good idea for extra search. This can be done in Dreamweaver through the use of Insert -> Form, then the text-fields etc, but make sure to set the method to GET and action to search_result.php
3> In search_result.php, create a recordset and use the QuB. In the table that shows the table structure, make sure to select the column that you want the search function to check only. Under 'condition' (below), click the right-hand '...' box. In the ensuing window, set condition to "contains", run-time value as "0" and default value as the text-field 'id' created at search_form.php earlier.
4> Go to "Query" menu at top left corner and "save" then "close". Click 'Advanced to see the query something like below:-
SELECT multiply_test.multiply_id (row 1)
FROM multiply_test
WHERE multiply_test.multiply_id LIKE %KTColParam1% (row 3)
change row(1) to " SELECT * " to properly reflect all fields.
and change the %ColParam1% properties of run-time value to $_GET['x'] ,where x is search_term id as mentioned earlier.
5> Walah..the search function is done! You can use multiple search terms by manipulating the MySQL query by adding 'AND' and 'OR' with other columns at row 3.
Hope people reading it can understand it and if not, again, email me!
Thursday, July 2, 2009
MySQL Values From Two Tables - 2nd July
Recently I faced a problem with getting values from two(2) MySQL data at the same time. Using the common method of getting data will not work in Dreamweaver as the common method is only able to compare between:-
1 - URL Parameters w/ $_GET['x']
2 - Form variable w/ $_POST['x']
3 - Cookies w/ $_COOKIE['x']
4 - Session variable w/ $_SESSION['x']
5 - Server variable w/ $_SERVER['x']
6 - Entered value
Let me provide an example:-
You have table A to record user 'input items id' and you have another which displays the item full details such as name and price etc. And you have a webpage to display the items that the user has in the records. So, by calling the item id that the user has, the information of the particular item can be displayed without having to put all of the information into the 'input items id' form.
As you can see, none of the methods fits the purpose.
To do exactly what you wanted would require this MySQL query:-
(and they must be linked in the same recordset of it won't work)
SELECT item_info.*, user_saved.*
FROM (item_info LEFT JOIN user_saved ON user_saved.id_1=item_info.id)
WHERE user_id = colname
and the configurations for 'colname' would have to be:-
(After pressing "+" sign near 'variables' to add new variable)
Name:- colname
Type:- Integer
Default Value:- -1
Runtime Values:- $_GET['user_id']
And that's it! Run your page, click on the link with defined id and you've linked and obtained data from two totally different tables. Oh, and for all I know, this works with the entire recordset, meaning you can use the recordset 'bindings' as you wish. Email me if you're unsure of what is written above.
1 - URL Parameters w/ $_GET['x']
2 - Form variable w/ $_POST['x']
3 - Cookies w/ $_COOKIE['x']
4 - Session variable w/ $_SESSION['x']
5 - Server variable w/ $_SERVER['x']
6 - Entered value
Let me provide an example:-
You have table A to record user 'input items id' and you have another which displays the item full details such as name and price etc. And you have a webpage to display the items that the user has in the records. So, by calling the item id that the user has, the information of the particular item can be displayed without having to put all of the information into the 'input items id' form.
As you can see, none of the methods fits the purpose.
To do exactly what you wanted would require this MySQL query:-
(and they must be linked in the same recordset of it won't work)
SELECT item_info.*, user_saved.*
FROM (item_info LEFT JOIN user_saved ON user_saved.id_1=item_info.id)
WHERE user_id = colname
and the configurations for 'colname' would have to be:-
(After pressing "+" sign near 'variables' to add new variable)
Name:- colname
Type:- Integer
Default Value:- -1
Runtime Values:- $_GET['user_id']
And that's it! Run your page, click on the link with defined id and you've linked and obtained data from two totally different tables. Oh, and for all I know, this works with the entire recordset, meaning you can use the recordset 'bindings' as you wish. Email me if you're unsure of what is written above.
Subscribe to:
Posts (Atom)