MySQL Query To Order By User's Posts Instead Of Name
I have a script for users who post articles. The script shows the list of users ordered by name but I would like to order it by number of articles written.
I was hoping if somebody can help me figure out the change in the code to do this. I thought I could just change the ORDER BY argument in the query but the coder wrote it differently because the posts are in a different table, so I am not sure how to do it.
Here is the code:
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Displays 5 Posts Per Page In Descending Order
I have been doing up a website which will, among other things, display news that I input into the MYSQL database. I have a working version of this where it just displays 5 posts per page in descending order (the latest at the top of the page) with the page numbering underneath them. Now what I wan't to do is just dispaly the latest 5 posts on the page but, I want to dispaly them so that I can put in a Javascript function to maxamize and minimize each post by clicking on the title. So, I have, ID, Title, Newstype, Author, Post, Date. In my mind I see this as display each of these using the COUNT(*) thing and just subtracting 1, 2, 3, 4 to get the 5 posts I want. I just don't know how to do it.
View Replies !
View Related
Mysql Query ORDER BY
my code has been working fine but for some reason today it stoped. $search = mysql_query("SELECT `id` , `site_title` FROM `sites` WHERE `accepted`='1' ORDER BY `site_in` DESC LIMIT $lima, $limb"); while (mysql_fetch_row($displaySearch)){ // data,,, But it isnt displaying this correctly. :s its displaying like 3 then 25.
View Replies !
View Related
Build A Mysql Query Selecting All Users
I have two fields in my table "users" one field is called "category1" and the other is "category2". I'm trying to build a mysql query selecting all users that either have category1 or category2 containing "dancer". So basically I want all results for category1 containing "dancer" and category2 containing "dancer" to show up. Basically if they have "dancer" in either category they will show up for the query result. How can I build this query?
View Replies !
View Related
Run A Query On The Db To Count The Posts, Then Echo It In The Flash.
I'm looking to create a very small .swf which displays how many posts/topics/members there are in my forum - but Im looking for it to update in real time. So as posts are made on the forum, if someone is viewing the homepage they see the number update 'live' I'm wondering if anyone can tell if this is hard to do? I'm fine with PHP and am just starting to learn flash, but so far only have done minor animation. Would it work by embedding a PHP script in the flash to run a query on the db to count the posts, then echo it in the flash. But how would I get it to update live?
View Replies !
View Related
Order A Query
I have an easy question (I hope). I am trying to order a query, the field to order will be "sub" (which is an int) but I only want to put sub=0 up front, and all the rest behind but not in numeric order. right now, I have: ORDER BY sub, name so it sorts by sub, and in the event of a tie, it sorts by name. I would like to have all the sub=0 first sorted by name, then all the sub>0 after, sorted by name. Can I do this in ORDER BY or do I need a script?
View Replies !
View Related
Order BY Query
I have numerous tables that are searched by the following query: $query = "SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM out2006 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn' UNION ALL SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM indoor200506 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn' UNION ALL SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM out2005 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn' UNION ALL SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM indoor200405 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn' UNION ALL SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM out4 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn' UNION ALL SELECT date,meet,fname,sname,event,perf,mid,perf2 FROM indoor3 WHERE fname LIKE '" . $fn. "%' AND sname ='$sn' ORDER BY 'event'"; I have tried to then run an ORDER BY query, byt fails. The above produces a list of athletes performances, I just want them ordered by "event " and "perf," "mid," "perf2."
View Replies !
View Related
Query Order Problem
I've got a table in my dB which looks like this: id | name | project | type | catagory | keywords | description | image | live | timestamp and using this code: PHP Code: $query_Recordset1 = "SELECT * FROM datacore GROUP BY project ORDER BY `timestamp` DESC"; I'm getting a weird order. The idea here, is to get back the most recent file for each project based on the timestamp. I've looked over the numbers and my results bring back 2 projects. I only have two projects, so that's fine, but the row that's coming back from each project isn't based on the timestamps within each project. I think this is because it's grouping the results first, then it's ordering the results by the timestamps. What I want it to do is order by timestamps, then get the newest image for each project.
View Replies !
View Related
Order Query Results Alphabetically
I need to order query results alphabetically BUT I also need to start showing them starting from one of these values. eg. if my records are: yellow, brown, red, green, white. I need to show "red" records first and then other ones in alphabetic order: red brown green white Is it possible to do this using a query only?
View Replies !
View Related
SQL Query For Getting All Users From Different Tables
I've got these two tables; users(pic,username,id) and votes(voter_id, voted_id, id, rating) What I'm trying to do is getting all users from table 1 that weren't voted before by the user who's browsing the script. The closest I've got is this SELECT pic,username,id FROM users WHERE id IN (SELECT `voted_id` FROM `votes` WHERE `voter_id` <> '$userid')" But this isn't enough, as there might be some users that were voted by people different from $userid AND by $userid himself...
View Replies !
View Related
Create A Query In Order To Dynamicly Popular A Listbox
I am trying to create a query in order to dynamicly popular a listbox however I am trying to fill the listbox with years such as 2007 etc based on if they are stored in the data base. The problem is that two tables have to be checked that have no relation to eachother but this caues the problem of duplication so if both tables have 2007 it will appear in the list more than once. Code:
View Replies !
View Related
Recreate The Query For Each Day In Order To Show The Result In A Table
I would like to get a count for every element filled on that day but however i am unsure how to make this query to the mysql db. for example every thing filed on a monday or for each given day of the week may be better. table field is called date and is in datetime format Y-d-m H:i:s how would i complete this query to get it to look for each even if i had to recreate the query for each day in order to show the result in a table. PHP Version 4.3.11 being used PHP Code: Select count (something) as total from database where ?
View Replies !
View Related
Query Returns Unexpected Users
Having a bit of a problem trying to get the correct results. Basically I have three tables I need to select from; user, platform and game. I'm running the following query Code: select distinct u.user_name, u.user_avatar, u.user_rating from user_game ug, platform p, user u where ug.game_id = 1 and p.platform_id = 1 and u.user_country = 1 Basically I want it to return users who own the game "1", have it on platform "1" and live in country "1". Unfortunately does not return users who meet ALL WHERE criteria. Any ideas where I am going wrong? I know an alternative is to use JOINS but I don't want the performance to be hit by doing that.
View Replies !
View Related
How Do You Do No New Posts/new Posts Function
On forums you have the little image at the side. Usually one is faded which means no new posts and the other is the same image but darker which means new posts. How do you go about logging when people have looked in a forum. Surely you can't have a table with an activity time for every forum as this would be imense.
View Replies !
View Related
Error In Query: SELECT Uid, Uperms From Users WHERE Uname =
I am trying to set up the article from the main page in devshed called money is time the thing is this, it denies access for this user and pass even though they are in the database 'example' and on table (users) Error in query: SELECT uid, uperms from users WHERE uname = 'john' AND upass = PASSWORD('john'). Access denied for user: '@localhost' to database 'example' Code:
View Replies !
View Related
Query That Logs Onto The Datbase & Retrieves A Users Email Address
I'm trying to create a query that logs onto the datbase & retrieves a users email address and then displays it. Here's the code; <? print "Your email adddress is " ; $conn = db_connect(); $result = $conn->query("select email from user where email='$email'");?> I've had the following response :Catchable fatal error: Object of class mysqli_result could not be converted to string.
View Replies !
View Related
ORDER BY MySQL
If you're dealing with ID #'s, or with numbers.. Make sure you are using INT instead of VARCHAR (which is probably obvious, but I had to find out the hard way). If you use INT you can sort the numbers correctly.. 1 2 3 22 33 with VARCHAR you'll end up with 1 2 22 3 33
View Replies !
View Related
MySQL & PHP Order
I have a content page requesting data from MySQL. I have it working, however, not the way I want to. Whenever I add a new row to the tables, the new row adds, but BELOW all the rest of the rows. For example... This is an existing row in a table 1 This is an existing row in a table 2 This is an existing row in a table 3 This is a NEW row that I just updated I want it to be like this: This is a NEW row that I just updated This is an existing row in a table 3 This is an existing row in a table 2 This is an existing row in a table 1 This is the code I have for it currently: Code:
View Replies !
View Related
MYSQL IN() Order
The following queries return the results in the same order 10, 11. How can I get it to return the results in the order I specify inside of IN() ? SELECT * FROM `users` WHERE `id` IN (11, 10); SELECT * FROM `users` WHERE `id` IN (10, 11);
View Replies !
View Related
MySQL ORDER
Code: SELECT DISTINCT * FROM table GROUP BY paid DESC, rand() The sql statement above is what I am using to display a full listing of all people in the database. That I want to do is list ALL people FIRST that have paid. The paid column has dates that the persons account expires. example: 2006-07-01 What I am attempting to do is NOT list by the 'paid' expire date but instead, the client_id (first column which is autoincrement) but still have the listing show the people that have 'paid' first, then all others that have not paid in random order.
View Replies !
View Related
ORDER BY Problem (mySQL)
Is it possible to use two fields at the same time in an ORDER BY statement, using mySQL? Let's say I have a table "user": userId: int userNick: varchar 255 userNickSpecial: varchar 255 Sometimes, I'd like to be able to sort the users using BOTH the "userNick" and "userNickSpecial"! This: SELECT * FROM user ORDER BY userNick, userNickSpecial; will sort by userNick and then by userNickSpecial. I'd like the sort to be done on both fields at the same time! Ex: ----- userId: 1 userNick: 'bbb' userNickSpecial: '' ----- userId: 2 userNick: '' userNickSpecial: 'ccc' ----- userId: 3 userNick: '' userNickSpecial: 'aaa' ----- userId: 4 userNick: 'ddd' userNickSpecial: '' ----- Would sort the users like this: userId: 3 userId: 1 userId: 2 userId: 4 Is it possible?
View Replies !
View Related
PHP MySQL Order Results
I'm using: $sql = "SELECT * from $myTable ORDER BY `points` DESC LIMIT 5"; in a whileloop to display the top five scorers but would like to put their position next to the results (eg, 1, 2, 3, 4 and 5) Is there an easy way to do this?
View Replies !
View Related
MySQL Order By Two Columns
I got my results to order the way I want. Which is ORDER BY verified DESC. But after that I want to order another column in ascending order. I tried ORDER BY verified DESC, device and that didn't work. I also tried ORDER BY verified DESC ORDER BY device. Does anyone know how I can do this?
View Replies !
View Related
MySQL ORDER BY Issue
The sql statement above is what I am using to display a full listing of all people in the database. That I want to do is list ALL people FIRST that have paid. The paid column has dates that the persons account expires. example: 2006-07-01 What I am attempting to do is NOT list by the 'paid' expire date but instead, the client_id (first column which is autoincrement) but still have the listing show the people that have 'paid' first, then all others that have not paid in random order. Does anyone know the proper format (if one exists) that would show the 'paid' people in order of client_id instead of in order of the 'paid' date?!?!?
View Replies !
View Related
MySQL To Order Alphabetically
I have two queries which are pretty much exactly the same - just accessing different tables - they both use ORDER by column_name ASC. Both queries are ordering based on a column of name fields - none of which are NULL. This doesn't order the results alphabetically, but works fine otherwise: $query_product_name = "select * from product where product_id LIKE '".$product_id[i]."' ORDER BY product_name ASC"; And this does work: $query = "select * from textile_category ORDER BY textile_category_name ASC"; The only difference is that the one that works doesn't have dynamic variable-base input. I really need the product names to be ordered alphabetically.
View Replies !
View Related
MySQL ORDER BY With GROUP BY
I have a table that contains duplicate data. I am trying to pull all of the most recent rows from the table SELECT * FROM `data` ORDER BY 'timestamp' GROUP BY 'name' This query produces SQL erros. How can I pull out the "latest version" of all the names in the table.
View Replies !
View Related
Order Dates From MySQL
I am trying to order my results from my MySQL database in date order using the usual way: $sql = "select * from $table ORDER BY 'date_added'"; This was working fine until it changed to a new month and now it's not ordering them in the correct order. ie anything with this months date is now appearing at the bottom when it should be most recent at the top of the results.
View Replies !
View Related
MySQL - Using Expressions In ORDER BY Statement
I am currently teaching myself PHP/MySQL to add functionality to a website showing event listings for an entertainment venue. The events in any day can run from 11am until 3am. I have a search script that will return all events by title, time, date and other criteria and users can click on any column heading in the results to sort by that column. My problem is the event start time column. Sorting the data using "ORDER BY time" puts the post-midnight shows at the beginning of the list not the end. Does anyone have any ideas as to how I could adapt the ORDER BY part of the SELECT statement to return the post-midnight shows at the end of the list. I could presumably sort after deducting 4 hours off every time. Would something like ORDER BY (time-4) work (using simplistic shorthand for the "time-4")? I assume I would have to use the DATE_SUB() command but I am not sure about the format. The time is currently recorded simply as hh:mm:ss in the MySQL database.
View Replies !
View Related
Selecting A Mysql Row Set In A Random Order
I have implemented a links bar on the side of a site, which I want to popuplate with links from a database randomly, but I feel the php workaround I used is inefficient. Is there a way to randomize the result in a mysql SELECT query? "SELECT * FROM links ORDER random" I know the code above is probably way off, but it illustrates what I want I think.
View Replies !
View Related
Mysql Messes Up ORDER BY Optimisation?!
I have a pretty long query that ends with ORDER BY R.r_recent_hits DESC LIMIT 0, 1 I also have an index on R.r_recent_hits. I did an explain select and got this: ALL - which means (from the manual): A full table scan will be done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. My question is, why in the world would MySql choose to do this type of join when there is a perfectly good index there! And I have run analyse on all of the tables. It seems like it would be a no-brainer, as soon as the optimizer sees ORDER BY, just use the given index for that column, that will get you the highest value easily. Does any one know why it would not automatically do that? The query essentially does nothing but selects the highest value. If i suggest what index to use by saying USE INDEX, it works fine. But, I don't think i should have to. if you are interested, here is the query: EXPLAIN SELECT H.accumulated_hits, R.num_messages, R.r_recent_hits, T.topic_scope, R.thread_id, U.username, U.user_id, T.title, UNIX_TIMESTAMP(T.post_time) AS post_time, T.topic_id, T.description FROM threads as R, topics AS T , users AS U, hit_counter AS H WHERE T.topic_id=R.topic_id AND H.object_type='THREAD' AND H.object_id=R.thread_id AND U.user_id=T.user_id ORDER BY R.r_recent_hits DESC LIMIT 0, 1
View Replies !
View Related
MySQL Order By For Varchar Column
I have a VARCHAR column where most rows start with numbers; however, a few rows start with letters. I have ordered the rows to appear in descending order. SELECT * FROM db ORDER BY varcharcolumn DESC With this query the numbers appear in the desired order, but the letters appear before the numbers. This order makes sense based on my query but I don't want the letters before the numbers. Is there a way to force the letters to appear after the numbers?
View Replies !
View Related
Mysql ORDER BY And Levenshtein() Function
i am writing a simple search using the levenshtein() function. this is returning an array of nearly-matching IDs which i am using to search the database. is there any way i can order my select statement in the same way which the levenshtein array is ordered? at the moment my (simplified) sql is: SELECT ID, title FROM table WHERE ID IN($my_imploded_levenshtein_array) however, this is ordered normally by ID, instead of the same order the levenshtein array is in. i had thought of creating a temporary table for all the data, but as i am selecting data from 3 joined tables, i think this would be a bit cumbersome.
View Replies !
View Related
MySQL / Distinct / Order By Question
I have a MySQL table, where there are x number of records. Let's say each record contains a "city" field, which may be New York, London, Paris, Madrid etc., and in other fields there is other data. I want to construct a query which gives a list of distinct cities in the table: "SELECT DISTINCT city FROM table ORDER BY city ASC" which gives the result "London, Madrid, New York, Paris". So far so good. However, I want to LIMIT the list to the 3 most common cities in the table.
View Replies !
View Related
MySQL Database In Sorted Order
Here's an example of my database Key ID - Name - Amount 0 - Test - 865 1 - Blah - 2658 2 - Hello - 865 3 - Bye - 458 I would like to sort this database by the column Amount and echo the following: Blah (2658) Test (865) Hello (865) Bye (458) If there's 2 of the same value's in "Amount" I don't care how it sorts it as long as it's in between the next highest Amount and the previous Lowest amount.
View Replies !
View Related
MySQL Users
Is it a good idea to have multiple user account? for example, say an admin user logs into your website they establish a connection with full access. a regular user logs in with an account that only allows insert and updates. Do you do this? Is it work the extra code?
View Replies !
View Related
Mysql UNION - Order By `date` Desc?
my query: SELECT * FROM `table1` UNION ALL SELECT * FROM `table2` ORDER BY `date` desc when i am getting the data using mysql_fetch_array, how can i tell which table the row is from? how can i get everything to order by `date` desc? like right now, that does order it by date desc, except it does the 1st table 1st, the 2nd table 2nd.
View Replies !
View Related
How Can I Add And Edit Mysql Users?
after installing my php5,mysql,and apache. i cant seem to get my php application to connect with mysql database. i am new to mysql and honestly speaking i really dont have a clue how to deal with mysql. can anyone help me? how can i add and edit mysql users? where should i go if i want to add new databases?
View Replies !
View Related
Order Query By Number Only Reads First Number .
I have a result page which has price lists, but for some reason the code cannot distinguish between 25,000 and 5,000. Another words in a DESC price result, i get: 80,000 78,000 72,000 6,000 52,000 42,000 It reads the first number, and if first number is the same it reads second, but how do i get it to Order by the entire Number?
View Replies !
View Related
Inserting Data Into A Users Row In Mysql
i'm not sure if this is possible but this is what i'm attempting to do: 1)I Have made a registration page for a user to fill in and submit <-- ok 2)Once they do they can login to thier account <-- ok 3)the minute they are in thier account they can upload a photo/avatar and it submits the path/filename e.g to the database "grahamspicture.jpg" for example. but what i'm not sure about is HOW to post that logged in users data back into the mysql database, once that particular user logs in how to make mysql know to post the path in grahams mysql row under photo (i hope that made sense) Code:
View Replies !
View Related
Echoing Users Data From Mysql
when users on my site look at they're profile details some of the data in like this: <select name="somename"> <option value="Option 1">Option 1</option> <option value="Option 2">Option 2</option> </select> once a user has selected an option and it's stored in mysql i was wanting the specific information to be displayed in the drop down box, if i selected option 2 next time i log in option 2 is displayed and so on. what would be the best way to achieve this?
View Replies !
View Related
Sending Mail To Users In Mysql DB
I have a php script setup to fetch records from a mysql database and send a message to all the users returned using sendmail() command. This process does it by looping through each individual record returned by the query and takes about 2 minutes to finish on my server. Does anyone know of a faster method to do this?
View Replies !
View Related
PHP Update Query Mysql: Query Succussful?
I can't seem to successfully test an update query. Below is a piece of code that updates a statistics table. If row today doesn;t exist, a new day must be created. But this doesn't work... thank you for your reply. $today= date("Y-m-d"); $qq="update totals_r set hits_r=(hits_r+1), lang_$lang=(lang_$lang+1), cat_$cat=(cat_$cat+1), rating_$rating=(rating_$rating+1), bussite_$bussite=(bussite_$bussite+1) where date='$today'"; $result_6 = mysql_query ("$qq"); if (!$result_6){ /// IF !ROW_TODAY ==> CREATE NEW DAY $qq_2="insert into totals_r (hits_r, lang_$lang, cat_$cat, rating_$rating, bussite_$bussite, date) values(Ƈ',Ƈ',Ƈ',Ƈ',Ƈ','$today')"; $result_6_2 = mysql_query ("$qq_2"); if (!$result_6_2){ $err_msg_sql_6= mysql_error(); $err_loc_6="$PHP_SELF"." // Query6: update totals_r"; } }
View Replies !
View Related
Query - Create The Mysql Query To Get All Fields
I've a table "article". Any article is of a type in table "type1" or "type2" Each type may be of a model in table "model1" or "model2". Each model can be on a certain make, also in 2 tables, "make1" and "make2". they are a few possibilities. "article" can be of "type1" or "type2" "type1" can be of "model1" only - "type2" can be of "model1" or "model2" "model1" can be of "make1" only - "model2" can be of "make1" or "make2" "article" has "type.id" field (char10). If it start with "my" then it's a "type2", otherwise is a "type1" "type1" has only "model1.id" field. "type2" has "model1" and "model2" fields. If one is filled, the other isn't "model1" has only "make1.id" field. "model2" has "make1.id" and "make2.id" fields. If one is filled, the other isn't so here are the possibilities: article -> type1 -> model1 -> make1 article -> type2 -> model1 -> make1 article -> type2 -> model2 -> make1 article -> type2 -> model2 -> make2 Now, I must create the mysql query to get all fields given the "article.id". Is it possible ? How ? will this work ? in pseudo-code article left join type1 articleid = type1id inner join model1 type1id = model1id //inner because if type1, the model is 1 inner join make1 model1id = make1id //inner because if model1, the make is 1 left join type2 articleid = type2id left join model1 type2id = model1id left join model2 type2id = model2id left join make1 model2id = make1id left join make2 model2id = make2id
View Replies !
View Related
Prevent Users From Requerying The MySQL Database.
I am building a simple forum/disussion area for a website. When a user posts a new message, they type info into a FORM which opens another page that queries the database (performing an INSERT INTO... statement). This works fine... However, if the user RENEWS the final page in their browser, the database is requeried with the same information that was posted from the FORM! (Thereby storing repeat information) How can I prevent this?
View Replies !
View Related
|
TRACKER
