I have your run of the mill nested set hierarchy type setup with the following columns: table name: myset columns: id, name, lft, rgt Does anyone know a query to determine the parent of a node? I read a couple places that it's handy to also have a *parent_id* column in your table to keep track of this, but it seems redundant and ...
I've got a table recording views of programs. Each program can have two different types (1=video, 2=audio) My table to record the views: TABLE views view_id user_agent created_at type program_id (which related to table programs) My current MySQL call: SELECT COUNT(*) as views, `mp`.`title`, `mp`.`program_date` as da...
I am joining a few tables for a selection If there isnt anything matching in the 2nd, 3rd, 4th tables I still want to pull the results as long as the first table has a match. I thought LEFT JOIN did this, but it is not. Here is the full query: SELECT cart_product.*, prod_drop_products.prod_drop_product_name, everlon_sheet.*, cart_prod...
I'm looking to be able to run a single query on a remote server in a scripted task. For example, intuitively, I would imagine it would go something like: mysql -uroot -p -hslavedb.mydomain.com mydb_production "select * from users;" ...
I asked this question on the CakePHP google-group but didn't find any resolution, so hopefully will have better luck here. I've been developing on CakePHP for a bit and decided to have a localhost version on MAMP so that I could demo my app to people without being dependent on an Internet connection. We have a couple of complex MySQL q...
Hi all, I'm working with the mediawiki API ( e.g. http://en.wikipedia.org/w/api.php) and I would like to be able to 'truncate' the mysql tables in order to reset the local installation while keeping some tables (users, ?...). What would be the SQL queries ? I would say: tuncate all the tables but ${PREFIX}_user and update ${PREFIX}_us...
Hello. I'm a novice when it comes to SQL and PHP, and I'm trying to round an average price result and take off the extra zeroes that currently appear. Currently my result turns up as: $3.005000 My code currently reads as follows: $result = mysql_query("SELECT AVG(price) FROM milk WHERE price > 0 "); $row = mysql_fetch_row ($result); ...
I am writing this comment class: class Comment { public $id; public $post_id; public $name; public $email; public $website; public $body; public $date; public $ip_address; public $status; function __construct($id) { global $db; $resc = $db->query("SELECT * FROM blog_comments WHERE id...
I know there are a number of "How do I find the most recent record" questions out there, but none of them quite solved my particular problem: in MySQL, I'm trying to find the most recent record for an entry that's mapped to two different categories in the same table. There's essentially an ENTRIES table with a bunch of info, a CATEGORIES...
How can I get the number of a day in a week fro example: 2009-10-21 would be 4 assuming that: Sun - 1 Mon - 2 Tue - 3 Wed - 4 Thu - 5 Fri - 6 Sat - 7 I am using MySql Server 5.0.37 ...
I've used MySQL (via PHPMyAdmin) a lot before but never really understood half of it. I'm assuming that for varchar, length is the maximum length of a string that can go there. But what about for Int? According to this, Int is a 4 byte integer, so why have a Length parameter for it? Is it the number of bits for that integer? Why have sep...
Hi all; I searched a lot but could not find a way to dump table relations like one-to-one, one-to-many vs in PHP. Is there a way to handle this issue in PHP? A result might be: array( 'tableA' => array( 'one-to-one' => array('tableB', 'tableC'), 'one-to-many' => array('tableD'), 'tableB' => arra...
So I have a database table in MySQL that has a column containing a string. Given a target string, I want to find all the rows that have a substring contained in the target, ie all the rows for which the target string is a superstring for the column. At the moment I'm using a query along the lines of: SELECT * FROM table WHERE 'my supe...
I have two tables, "name" and "address". I would like to list the last_name and joined address.street_address of all last_name in table "name" that occur more than once in table "name". The two tables are joined on the column "name_id". The desired output would appear like so: 213 | smith | 123 bluebird | 14 | smith | 456 first ave...
Hello to all, I am setting up a system to host WordPress blogs for users of a site I run. Right now, things are functioning pretty well within one database and different blogs running with their own prepended tables (user1_posts, user_posts, etc). Despite this working so far, it feels a bit messy. If this database were to have 4000 t...
I want to add 1 month and 3 months, should I do like that: UPDATE `set` SET expire = DATE_ADD(`expire`, INTERVAL 1 MONTH) WHERE ID='$lid' UPDATE `set` SET expire = DATE_ADD(`expire`, INTERVAL 3 MONTHS) WHERE ID='$lid' is that right ? 3 MONTH or 3 MONTHS ? ...
I'm using the following setup: public MySQLProcessWriter(Connection con) throws SQLException { String returnNames[] = {"processId","length","vertices"}; addresser = con.prepareStatement("INSERT INTO addressbook (length, vertices, activity) VALUES (?, ?, ?)", returnNames); } processId corresponds to an auto-incrementing column in th...
A user can post 4 times at most within a day. The difficulty is how to get the start of a day in database?I'm using MySQL ...
I am attempting to populate a list of records within the last X seconds of the server time. My current query is.. mysql_query("SELECT player.name FROM player, spectate WHERE (player.pid = spectate.player) && spectate.bid=$bid"); This works currently to retrieve all entries. My first thought was to select the time field as well. And...
Hi, I have 2 tables catelog and catelog copy. And, I need to to display both these table fields as one table using mysql. Can anyone correct this code for joining 2 tables? $result = mysql_query("SELECT * FROM catelog WHERE title='".$fileid."'union select status from catelog_copy " ); ...