Wordpress Database Help Needed!
I'm trying to import some content into the wp_posts database, which works, but I can't figure out how to make it automatically publish at a later date.
When I publish the imported content manually (eg. edit timestamp and hit publish) it works, but when I try and upload that into the database, it doesn't seem to register.
I'm doing some traveling soon and I don't want the blog to lay dormant! Here's my SQL for an example post:
Quote:
INSERT INTOwp_posts(ID,post_author,post_date,post_date_gmt,post_content,post_title,po st_category,post_excerpt,post_status,comment_status,ping_status,post_password, post_name,to_ping,pinged,post_modified,post_modified_gmt,post_content_filtered ,post_parent,guid,menu_order,post_type,post_mime_type,comment_count`) VALUES
(123, 1, ��-11-21 17:45:39', ��-11-21 23:31:15', 'I AM THE POST CONTENT', 'THIS IS THE POST TITLE', 19, '', 'future', 'open', 'open', '', 'this-is-the-post-slug', '', '', ��-12-01 17:45:39', ��-12-01 23:31:15', '', 0, '', 0, 'post', '', 0);`
So all times are set in the future, and the post_status is "future". Why is it not publishing when the time comes around?
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Advice Needed On Database Schema
I am trying to come up with a proper database schema for this situation that's at least 3 NF. I'm just having trouble with a few of the associations. First, here's the situation. I want to store information about all the cards in a Tarot deck. In the deck, there are two types of cards; namely the major and minor arcana. Both of these have some common information I want to store, and I want to be able to choose just a random card, but they also have some data unique to each, and I also would like to choose a random card of a specific type. For example, both types have an associated element. I will have an elements table probably with simply id and name, and perhaps an element_id in the main cards table. Both also have an associated planet and zodiac sign. Most of the major arcana cards have either a planet or zodiac sign, but not both. Also the association is a little different. With the minor cards, the planet is a specific decanate within the sign. I guess that might not matter too much though in the storage of the data, though again it might if I wanted to retrieve the planets associated with the decanates of a sign. Further, the major arcana have a specific Hebrew letter associated with each card, while the minor cards have no such associated letters. Finally, each of the major arcana cards are classified as elemental, planetary, or zodiacal. The minor cards have no such classification. So, how should I store these data? They are both cards, and have some common data, but both have very different data as well.
WordPress Query
I have been working on some radical concepts for my blog (which is WordPress), one of which is to place the most recently commented on post at the top of the heap (the Loop). So... I set about writing some WordPress functionality (in the form of a plugin) and I've successfully gotten the ID numbers of the posts I want in the order I want them. But that is before WordPress gets its fingers on it and resorts the dataset by ID number (which basically sets things back to reverse chronological by post as opposed to reverse chronological by comment. Setting aside the fact that this is WordPress (and if you understand how WordPress works, feel free to chime in), I've decided to instead try to devise a custom query. These are the tables (for the WordPress challenged ): CREATE TABLE wp2_posts ( ID bigint(20) unsigned NOT NULL auto_increment, post_author bigint(20) NOT NULL default Ɔ', post_date datetime NOT NULL default ��-00-00 00:00:00', post_date_gmt datetime NOT NULL default ��-00-00 00:00:00', post_content longtext NOT NULL, post_title text NOT NULL, post_category int(4) NOT NULL default Ɔ', post_excerpt text NOT NULL, post_status enum('publish','draft','private','static','object','attachment','inherit','future') NOT NULL default 'publish', comment_status enum('open','closed','registered_only') NOT NULL default 'open', ping_status enum('open','closed') NOT NULL default 'open', post_password varchar(20) NOT NULL default '', post_name varchar(200) NOT NULL default '', to_ping text NOT NULL, pinged text NOT NULL, post_modified datetime NOT NULL default ��-00-00 00:00:00', post_modified_gmt datetime NOT NULL default ��-00-00 00:00:00', post_content_filtered text NOT NULL, post_parent bigint(20) NOT NULL default Ɔ', guid varchar(255) NOT NULL default '', menu_order int(11) NOT NULL default Ɔ', post_type varchar(20) NOT NULL default 'post', post_mime_type varchar(100) NOT NULL default '', comment_count bigint(20) NOT NULL default Ɔ', PRIMARY KEY (ID), KEY post_name (post_name), KEY type_status_date (post_type,post_status,post_date,ID), FULLTEXT KEY simplesearch (post_title,post_content), FULLTEXT KEY post_related (post_name,post_content) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1406 ; and CREATE TABLE wp2_comments ( comment_ID bigint(20) unsigned NOT NULL auto_increment, comment_post_ID int(11) NOT NULL default Ɔ', comment_author tinytext NOT NULL, comment_author_email varchar(100) NOT NULL default '', comment_author_url varchar(200) NOT NULL default '', comment_author_IP varchar(100) NOT NULL default '', comment_date datetime NOT NULL default ��-00-00 00:00:00', comment_date_gmt datetime NOT NULL default ��-00-00 00:00:00', comment_content text NOT NULL, comment_karma int(11) NOT NULL default Ɔ', comment_approved enum(Ɔ',Ƈ','spam') NOT NULL default Ƈ', comment_agent varchar(255) NOT NULL default '', comment_type varchar(20) NOT NULL default '', comment_parent bigint(20) NOT NULL default Ɔ', user_id bigint(20) NOT NULL default Ɔ', comment_subscribe enum('Y','N') NOT NULL default 'N', comment_favicon_url text NOT NULL, PRIMARY KEY (comment_ID), KEY comment_approved (comment_approved), KEY comment_post_ID (comment_post_ID) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=10736 ; Now I've started with this: SELECT DISTINCT ( ID ) AS mainid, wp2_posts. * , comment_date FROM wp2_posts LEFT JOIN wp2_comments ON wp2_posts.ID = wp2_comments.comment_post_id WHERE comment_approved = Ƈ' GROUP BY comment_date ORDER BY comment_date DESC I'm not really sure that this is what I want to do though. Can a database expert help me here. I really don't want the DISTINCT but it seems to be the only way to get unique entries. Otherwise I'll have 3 of one entry in a row then a fourth later on and so on. If I can simply return the results in order of comment_date DESC and limit the results to one instance of each entry that would be groovy.
MySQL And Wordpress
I've been running my WordPress blog for awhile on the WordPress site. Recently I noticed that now my Earthlink provider will host WordPress on my site, so I could either move my blog there or to my husband's Linux machine (we're both trying to learn PHP and MySQL. So anyway, I was going to try setting up WordPress on my Earthlink host but it needs me to create a MySQL database before I can set up WordPress. I was going to try setting one up, but I'm not dolphin-safe yet. Can you point me to a good book or online tutorial? The ones in the sticky are a bit too much for me yet.
MySQL Limitations For Wordpress
Wordpress v1.5x has the ability to have multiple blogs in a single database. The changes are made in the wp-config.php file. The plan is to designate one "central" install of WP and use it’s MySQL database to power the WP installs on many sites. My concern is how robust is MySQL? Will the "central" MySQL database eventually break as I create multiple of WP installs across many domains? Also, will calls to the central database make my pages load significantly slower. One last question, in the wp-config.php file you are asked to type in the host of database. Normally this is left at the default setting of "localhost". Under a central database installation, is the host name changed to the domain name where the MySQL database for WP is created?
MySQL Problem With Wordpress 2.1
I get the following error on my Word Press 2.1 installation: WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1] SELECT cat_id, cat_name FROM Its where my links (blogroll) are on the right side of the page. Any clue as to what it means and how I can fix it?
Wordpress Auto Content Problem?
I'm trying to install a wordpress auto content updater. My wp-config.php file: <?php // ** MySQL settings ** // define('DB_NAME', 'vyskico_vyskidb'); // The name of the database define('DB_USER', 'vyskico_******'); // Your MySQL username define('DB_PASSWORD', '*********'); // ...and password define('DB_HOST', 'localhost'); // 99% chance you won't need to change this value define('DB_CHARSET', 'utf8'); define('DB_COLLATE', ''); // You can have multiple installations in one database if you give each a unique prefix $table_prefix = 'wp_' // Only numbers, letters, and underscores please! // Change this to localize WordPress. A corresponding MO file for the // chosen language must be installed to wp-content/languages. // For example, install de.mo to wp-content/languages and set WPLANG to 'de' // to enable German language support. define ('WPLANG', ''); /* That's all, stop editing! Happy blogging. */ define('ABSPATH', dirname(__FILE__).'/'); require_once(ABSPATH.'wp-settings.php'); ?> According to this, http://www.vyski.com/readme.htm, I should simply point the browser to http://www.vyski.com/rsstoblog1.php and it would install it. But it says "Table 'vyskico_vyskidb.wp_categories' doesn't exist". Then I checked phpmy admin: vyskico_vyskidb (11) * Vis wp_comments * Vis wp_links * Vis wp_options * Vis wp_postmeta * Vis wp_posts * Vis wp_rsstoblog * Vis wp_terms * Vis wp_term_relationships * Vis wp_term_taxonomy * Vis wp_usermeta * Vis wp_users It somehow wont create wp_categories, but it has installed wp_rsstoblog.
MySql For WordPress Import Error
i am having problems importing wordpress mysql database. I am helping my fren to host his site on my server however his mysql version was 4.1.21 but my server is 4.0.27. My host doesnt want to upgrade the mysql for now so I am pretty stuck now because I keep getting this error. CREATE TABLE `sk2_blacklist` ( `id` int ( 11 ) NOT NULL AUTO_INCREMENT , `type` tinytext NOT NULL , `value` text NOT NULL , `score` int( 11 ) NOT NULL default Ɔ', `trust` int( 11 ) NOT NULL default Ɔ' , `comments` text NOT NULL , `added` datetime NOT NULL default ��-00-00 00:00:00', `added_by` tinytext NOT NULL , `last_used` datetime NOT NULL default ��-00-00 00:00:00' , `used_count` int( 11 ) NOT NULL default Ɔ' , `user_reviewed` enum ( 'yes', 'no' ) NOT NULL default 'no', PRIMARY KEY ( `id` ) ) ENGINE = MYISAM AUTO_INCREMENT =225 DEFAULT CHARSET = latin1 ; MySQL said: #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT CHARSET=latin1' at line 14 Is there away to solve this? or is there a converter to downgrade database? Worst comes to worse I will need to manually import each field.
Help Needed With DB Structure
I'm finally get a grip on using databases in my scripts, but I'm still lacking a bit in exactly how best to construct my databases ... so I'm hoping that I might be able to get some help with that here I currently have a MySQL database with one table called "users." In it will be held the registration information for everyone who signs up to the site: username, password, date of registration etc. etc. Now I want to link that table with another table that will contain information regarding the car that the user drives. This information will be collected from a second form and will contain data such as type, make, year of construction, colour etc. etc. What's the best way of linking these two tables together in order to help with running searches or scripts in the future? I was planning on inserting the relevant username into the second table as well so that I can run searches across both tables ... but is that the best way of doing it?
MYSQL Help Needed
I am having troubles trying to create my first table in mysql I am using version 5.0 I have entered the code below in mysql: mysql>CREATE TABLE table_name ( -> column_1_name column_1_type column_1_details, -> column_2_name column_2_type column_2_details, -> ... ->); and i get the following error: ERROR 1064 (42000): You have an error in your SQL syntax;check the manual that corresponds to your Mysql server version for the right syntax to use near 'column_1_type column_1_details,column_2_name column_2_type column_2_details,' at line 2
Syntax Needed
I have a table consisting of word_nr (key) and example_nr. Now i want to find out how many entries for example_nr are returned where word_nr=x and word_nr=y. So i made something like: SELECT count(*) FROM mytable WHERE word_nr=x and word_nr=y
Query Needed
i have a little problem with figuring out how to make a query for a certain table. The table consists of two columns: word_no and example_no. It is very likely that an entry word_no refers to more than one entries in example_nr. Now i would like to find out how many entries with the same example_nr are there for word_nr x AND word_nr y together.
Timezone Help Needed
I am in Washington State, Pacific timezone. However, our business day runs from 7:00am-7:00am. I have found if I change the system time on the MySQL box to the Japanese Timezone this works in "fooling" the system into thinking 7:00am local time is midnight, the start/end of a business day.. the problem comes when trying to update our intranet website, with the differing timezones..the huge difference in time will not allow updates with our ppl who use front page. (yes, I know notepad would be a better choice then front page. Not my call) I have tried unsuccessfully to change the time zone in the [mysqld] & [mysqld_safe] sections of the startup INI file. When I try to force MYsql into the japan time zone the server fails to start and an error message pops up failed to start unexpectedly,
Performance Needed
I have two tables. One for products and one for reviews. I have setup a detail page which displays the various fields out of the products table and then looks for all reviews in the reviews table which match the products key and display the average. All good so far. I want to have a page which displays a whole list of the products and show their rating next to them but I'm wondering if making it search the reviews table and calculate the average for 50 products in one go is going to cause a bit of a strain on the server? Is there another way I can do this, perhaps storing the precalculated average in the products table but how would I go about doing that?
SQL Statement Needed
How would I change in a table every reference to "ren" to "stimpy"? If a value equaled "renTop" I want it changed to read "stimpyTop".
Two (rather Complex) Problems. Help Needed!
Problem 1 I am developing a system where members of a site can submit graphic designs to a themed pack. Members can then view and rate the submissions from 1-5. This has all been accomplished, but I have encountered one problem. When listing the themed packs currently in progress, I want to be able to list how many submissions there are, how many of these submissions have a rating above 3.0, and how many of these submissions are above 4.0. I've been able to flawlessly count how many submissions there are without referring to their ratings with this query: select p.id as id, p.title as title, p.body as body, p.reltime as reltime, p.quota as quota, count(s1.id) as subcount_ns, from packs as p left outer join submissions as s1 on s1.pack = p.id where s1.state > -1 and p.released = 0 group by s1.pack order by p.id asc; How can I also, within this query, count how many submissions have a rating above 3.0 and count how many submissions have a rating above 4.0? I'm not sure how to do this, since I have already joined the submissions table to the packs table. The ratings table (sub_ratings) looks like this: id | subid | userid | rating | time The subid column refers to the submission's id, the userid column refers to the id of user who rated the submission, and the rating is an integer between 0 and 5. -------------------------------------------- Problem 2 For the submission system, some users choose to collaborate with other users on one submission. In this case, both of their userids are grouped together in the "creators" column of the submission, being separated by commas. Here is an example: 1,43,65 This would be an example of three users having collaborated on one design. Is there any possible way that I could, within one mysql query, determine the usernames of each of those users and return it as a column with the requested submission? I'm looking for something along the lines of php's "explode" function, but in mysql. Thanks in advance for the help that anyone offers.
Help Needed Writing Query
I'm trying to make a query to return 10 sorted values, but I need to calculate the value to sort by, ie its not stored as a field in the table. So say I had some data like this... id basicrank time 1 5 40 2 7 33 3 3 55 etc. I want to sort by (basicrank minus (currenttime - time)), so newer records get some priority, returning highest score first. My query could then be something like this, given current time of 60. SELECT id FROM table ORDER BY (basicrank-(60-time)) DESC LIMIT 10
Stored Procedure Needed
I have a table named players with these fields: playerid {int} teamid {int} jersey {int} playername {varchar(50)} age {int} And a table named pitchcounts with these fields: id {int} gamenum {int} playerid {int} pitchcount {int} Now, what I need is a stored procedure to return the number of pitches each player pitched in a week where the weekno is passed and the days rest required and next availability date of the pitcher based on the criteria below: In-House Ages 13-16 (Babe Ruth) -------------------------------------------------------- Pitches Days Rest ------------------------------- 0-20 0 Rest 21-40 1 Rest 41-60 2 Rest 61-95 3 Rest ******************************************************** In-House Ages 11 & 12 (Majors-American, National & Continental) -------------------------------------------------------- Pitches Days Rest ------------------------------- 0-20 0 Rest 21-40 1 Rest 41-60 2 Rest 61-85 3 Rest No more than 125 pitches in a week. 4 12's can pitch but no more than 240 pitches no matter how many games in a week. ******************************************************** Travel Ages 8, 9, 10, 11 & 12 -------------------------------------------------------- Player can pitch up to three innings in a weekend. Two innings doesn't affect In-House the next day, but prior In-House pitching does apply. If 61 or more pitches on Thurs or Sat no travel that weekend. A travel manager must obtain approval from the In-House manager in order to pitch a player for more than one inning if the in-house team has a game following the travel team on the same day. If pitchers does pitch more than 1 inning in travel, pitcher limited to 60 pitches In-House. If travel follows In-House on same day, player can pitch in travel if no more than 60 pitches thrown in-house. These rules will be in place until may 1st. We want these rules to be followed in spirit as well as literally. If abuses are found, even if within rules, we will change them after may 1st. Under no circumstances is a player able to be made ineligible for In-House play. ******************************************************** In-House Ages 10 and 9'S in Majors (Majors American, National, Continental) -------------------------------------------------------- Pitches Days Rest ------------------------------- 0-20 0 Rest 21-40 1 Rest 41-60 2 Rest 61-75 3 Rest ******************************************************** Double A In-House (AA) -------------------------------------------------------- No more than 40 pitches in a game or 80 for a week. ******************************************************** Additional In-House Notes -------------------------------------------------------- If a pitcher gets to 85 (75 at age 10) pitches in a game in the middle of a batter, they can finish the batter only. Pitching results must be reported by email to Director within 24 hours of the game by the winning manager. Pitcher can not re-enter to pitch in the same In-House game once removed from the mound. Pitcher receives seven warm-up pitches before their first inning on mound and five every inning thereafter. Warmup pitches no not count toward total counts. Pitches to final batter, if count gets past game or week totals, don't count toward totals. 12 year olds in Continental League can not pitch. Rest Examples: if 20 or less on Monday, available on Tuesday if 21-40 on Monday, available on Wednesday if 41-60 on Monday, available on Thursday if 61 or more on Monday, available on Friday
Help Needed To Join Tables.....
I'm starting into mysql these days and I have some tables created function_detail +------------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------------+------------------+------+-----+---------+----------------+ | function_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | function_name | varchar(128) | NO | | | | | function_description | varchar(512) | NO | | | | | function_image | varchar(256) | NO | | | | | function_image_caption | varchar(128) | NO | | | | | function_extras | varchar(64) | NO | | | | +------------------------+------------------+------+-----+---------+----------------+ option_detail +----------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------------------+------------------+------+-----+---------+----------------+ | option_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | option_name | varchar(64) | NO | | | | | option_description | varchar(256) | NO | | | | | option_image | varchar(256) | NO | | | | | option_image_caption | varchar(128) | NO | | | | | option_extras | varchar(64) | NO | | | | +----------------------+------------------+------+-----+---------+----------------+ product_detail +---------------------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default |Extra | +---------------------------+------------------+------+-----+---------+----------------+ | product_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | product_name | varchar(64) | NO | | | | | product_short_description | varchar(256) | NO | | | | | product_long_description | varchar(512) | NO | | | | | product_series | varchar(64) | NO | | | | | product_heads | varchar(64) | NO | | | | | product_family | varchar(64) | NO | | | | | product_category | varchar(64) | NO | | | | | product_image | varchar(256) | NO | | | | | product_image_caption | varchar(128) | NO | | | | | product_catalog | varchar(512) | NO | | | | | product_status | varchar(64) | NO | | | | +---------------------------+------------------+------+-----+---------+----------------+ product_function_detail +-------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+-------+ | product_id | int(10) unsigned | NO | | 0 | | | function_id | int(10) unsigned | NO | | | | +-------------+------------------+------+-----+---------+-------+ product_option_detail +-------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+------------------+------+-----+---------+-------+ | product_id | int(10) unsigned | NO | PRI | 0 | | | function_id | int(10) unsigned | NO | | | | +-------------+------------------+------+-----+---------+-------+ product_technology_detail +---------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+------------------+------+-----+---------+-------+ | product_id | int(10) unsigned | NO | PRI | 0 | | | technology_id | int(10) unsigned | NO | | | | +---------------+------------------+------+-----+---------+-------+ these tables are meant for: (let's see if I can explain correctly) Product Details would contain all the details for a certain products Function Details would contain all the functions performed by these products. Technology Detail would contain all the technology embeeded in these products. Some products may have either the same technology or the same functions (with the same descriptions) but affirmatively not the same product details (description product name & etc). For Instance Product A would perform function a,b,c,d and e and would have technology X,Y and Z product B would perform function f,g and h and would have technology W, Y and Z. product C would perform function b,c and I and would have technology O,P,Q and X. How can I link the three tables? Meaning that product_detail ---> function_detail when the linkage between the two of them resides in product_function_detail? Here is something I've tryied SELECT function_id, product_id as producto FROM product_function_detail WHERE product_id = ƈ' ORDER BY function_id asc; SELECT distinct function_name, function_description, function_detail.function_id FROM (select function_id, product_id as producto from product_function_detail WHERE product_id = ƈ' ORDER BY function_id asc)as xref, function_detail WHERE producto=ƈ' BUT so far, no luck.
SQL SELECT Statement Help Needed
I have three databases, like so: 1. Visitors: vid(int) | vname(varchar) 2. Events: eid(int) | ename(varchar) 3. Participation: pid(int) | visitor(int from vid) | event(int from eid) The participation table is a criss cross reference table to associate visitors with events. Given the vid(from visitor table) how go i query the database to get all the events a given visitor is participating in?
M2M Relational Query Help Needed
I have 3 tables, below are a list of the significant fields in each. Stories * story_id * story_date Editions * edition_id * publication_id Story_Edition_Jct * story_id * edition_id What I need to do is create a list of all the stories that are newer than 30 days and have not been assigned to a given publication_id, but may or may not have been assigned an edition. Just to clear that up some more, a publication consists of many editions, so we're looking for stories that may have been published in other publications, but want to restrict them from being republished in a given publication. The following query is what I've come up with:
MySQL Join Needed
I'm pretty new to MySQL and I don't really get the whole join thing, although I understand the theory behind it I have problems when putting it into practice. Basically I'm using PHP with MySQL back end and I've got two tables: TEST QUESTION The primary key of Test is "testno" which is an auto_increment and question has "testno" as a foreign key, so I need a join that will insert the value of "testno" into question when ever a record is entered into TEST based on the "testname" which the user enters into a form. Let me try and clarify: $testname = $_POST['testname']; sql="INSERT into question test.testno where testname = '$testname'"; Obviously this will not work but what I need in effect is a query which carries out this function.
Packing Needed For MySQL
I am still playing around with databases. So here it goes: Is packing needed for MySQL (5.0)? I have a new DB, which already is 178 MB. Will that continue to grow, when I add and delete data? Does it reclaim freed space and deleted rows? If not, is there a (automated) way to pack tables using SQL or something simple, which I can call from my app?
Select Statement Needed
The sending page allows the user to choose which search criteria he wants, so this (and many other) pieces of the select statement may or may not be present. The statement always starts with SELECT * from logdata where driver = '$_COOKIE[username]' If the user has selected them and supplied the proper data for a search, other elements will be added to the query string. For example, it may look like SELECT * from logdata where driver = '$_COOKIE[username]' AND amount >= $_POST[txtLowAmount] AND amount <= $_POST[txtHighAmount] AND restaurant regexp??? I'm not even sure if that is where a regexp belongs or if that is how it should be used. Ultimately, I would prefer it be non case sensitive. Here are some examples of what I want: If the user string is "CHIL" Chili'strue Charlie'sfalse CHILtrue chfalse
Simple Code Needed
Just need a little code to transfer a user to a custom error page if connection to the database fails. Like: If(no connect){ header("location: error.php"); } Just need the "no connect" part I guess.
Lemans Terms Needed
So can someone tell me in lemans terms what does LEFT JOIN do in MySQL? I see it more and more and have read the manuals but still don't understand. Maybe it's a mental block going on.
UPDATE / REPLACE - Assistance Needed
I have a table called COMPANY which holds 5,000 records Fields: Company_ID (int:2), Name (varchar:100), State_2letter (varchar:2) i.e. 1, ABC Company, AL I have another table called STATE (51 records) Fields: State_ID (int:2), State_2letter(varchar:2), State_Name (varchar:100) i.e. 1, AL, Alabama I want to change the structure of the COMPANY table to hold the STATE's table STATE_ID record to correspond with the State_2letter. But in the same time change the STATE_2letter to be an INT format too. So, my COMPANY table will look like this... i.e. 1, ABC Company, 1 I just cant find my solution on how to to an UPDATE command correctly.
Complex Select (Possible Subselect Needed?)
I have a table, b5_assignment_lookup, that is used elsewhere as a lookup but I'm trying to use the data contained by itself here. The table: CREATE TABLE b5_assignment_lookup ( as_id int(11) NOT NULL auto_increment, as_blog int(11) NOT NULL default Ɔ', as_blogger int(11) NOT NULL default Ɔ', as_milestone enum('start','finish') NOT NULL default 'start', as_timestamp timestamp NOT NULL default CURRENT_TIMESTAMP, PRIMARY KEY (`as_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=222 ; By running this query, I get the following resultset: mysql> SELECT * FROM b5_assignment_lookup WHERE as_blog = ྕ' AND as_timestamp <= ��-09-30' +--------+----------+-------------+---------------+---------------------+ | as_id | as_blog | as_blogger | as_milestone | as_timestamp | +--------+----------+-------------+---------------+---------------------+ | 87 | 89 | 41 | start | 2006-05-01 00:00:00 | |208 | 89 | 41 | finish| 2006-09-02 11:55:27 | |209 | 89 | 103 | start | 2006-09-02 11:55:27 | +--------+----------+-------------+---------------+---------------------+ 3 rows in set (0.00 sec) What we have here is that Blogger 41 began writing on Blog 89 on May 1, and on Sep 2 Blogger 103 took over for Blogger 41. What I really need to grok is all bloggers who blogged all or a part of a range of dates. For example, I really want to find out which bloggers blogged on blog 89 for all or part of 2006-09-01 to 2006-09-02. I use this dataset as an example, but we might have completely different circumstances such as Blogger 20 being replaced on the third day of the date range, being replaced by blogger 29 for 10 days and then quitting due to lack of time and the blog going unmanned for 5 days before Blogger 20 decides to step back in on day 25. The flags are the start/finish, obviously.
Help Needed Merging 2 Different But Similar Tables
Can someone tell me how I can import tables from another non-Joomla mysql file into Joomla? Basically it is just from one mySQL database into another. I use phpMyAdmin to import and export the entire file but I don't know how to do queries. I tried exporting the source database and then renaming all the database names to match the ones I want to merge into but all that happened was a new table was created - no merging. I really just want to select the relevant 3 fields and match them up with the fields in the database table I want to import into. I have an old program that listed all the fields of database 1 in one column and database 2 in another and all I had to do was drag the fields across to make a match and the program did the rest. Perhaps there is a similar one out there? What I am trying to do is import all the articles I have built up using ccTiddlyWiki into Joomla. The only table names of relevance from the source file are: id - title- body IN other words, just the bare essentials. The target files has a table called jos_content with columns: id - title - title_alias - introtext - fulltext in that order and others but are not important. I think it is fairly simple but I am not trained enough. I have 200 items so it would save an enormous amount of time! It seems easy but I don't know enough about queries to do it well. I'm hoping that I can import the articles and then just go through the list in Joomla adding subtitles and so on to conform to Joomla database essentials.
Needed Help With Uninstalling MySQL 5 On Mac/Unix
I have MySQL 5 on my Mac 10.4 and I want to completely uninstall it because I think I have MySQL 4 running on it too. I'm not too sure how to check for this. I'm not the best with administrating a database.
Help Needed For Inserting Using Variables In A Loop
im trying to dod a simple insert into my database but the thing is instead of having stable variables i need to input balues from within a loop. heres the code but i cant figure out whats wrong with it: :mad: <? include "login.inc"; $i=1; while ($i < $num): $query="insert into A(B,C,D) values('$E$i','$F','$G$i'"; $result = MYSQL_QUERY($query); $i++; endwhile; print("thank you"); ?> so wot should happen is E1, F, and G1 are entered into the database the first time round and then E2, F, G2 and so on until i=$num.
Help Needed For Query Involving JOIN
I have two tables populated during the use of an application to log user events and application states. They are named "EventTable" and "StateTable" and the structures follow:
Backups Of Raw Db Files - Indexes Needed?
Right now I am backing up mysql 4.0.14 on Windows2000 with a scheduled batch file that shuts down the mysql service - copies the entire data directory to a ackup directory, and restarts the mysql service. this ackup directory then goes onto tape a couple of hours later via veritas remote backup agent. I have tested a restore of this backup method and it works fine. Now I would like to add some more indexes to a table that has 11 million rows, however the index files will be very large. Is it necessary to backup index files? In case a restore was needed, I could just recreate the indexes manually (via a batch file with the correct index commands). Will mysql choke if the table definition shows an index - but the index file is missing? are there any "bad things" that will happen if index files are missing?
Libmysqlclient.so.10 Is Needed By (installed) Mod_auth_mysql-1.11-12
Redhat 9.0 Mysql 3.23.56 ==> Running I want to upgarde to 4.0.13 but this is the error it says: [root@localhost downloads]# rpm -Uvh MySQL-server-4.0.13-0.i386.rpm warning: MySQL-server-4.0.13-0.i386.rpm: V3 DSA signature: NOKEY, key ID 5072e1f5 error: Failed dependencies: libmysqlclient.so.10 is needed by (installed) mod_auth_mysql-1.11-12 libmysqlclient.so.10 is needed by (installed) perl-DBD-MySQL-2.1021-3 libmysqlclient.so.10 is needed by (installed) php-mysql-4.2.2-17.2 If i install with -i will it install with the backward compatibility, it should not break those dependencies.
Backups Of Raw Db Files - Indexes Needed?
Right now I am backing up mysql 4.0.14 on Windows2000 with a scheduled batch file that shuts down the mysql service - copies the entire data directory to a ackup directory, and restarts the mysql service. this ackup directory then goes onto tape a couple of hours later via veritas remote backup agent. I have tested a restore of this backup method and it works fine. Now I would like to add some more indexes to a table that has 11 million rows, however the index files will be very large. Is it necessary to backup index files? In case a restore was needed, I could just recreate the indexes manually (via a batch file with the correct index commands). Will mysql choke if the table definition shows an index - but the index file is missing? are there any "bad things" that will happen if index files are missing?
Whats Needed On Client PC And Server?
I'm currently using MySQL 5.0.9 with Visual C++ 6.0 and Qt. My project is on the server, with all the dll's needed as well as MySQL with my database. I can only access my database on the server from a client station if this client station also has MySQL installed. It doesn't even execute the queries in my code. Do I need to use server scripts and if so how do I do it? Is there in Windows something like a mysql-server and mysql-client package? Do we need to install MySQL on all of the client stations?
MYSQL Query Speed + Explanations Needed
Sometime I need to delete results set of the some query. Like DELETE FROM table WHERE id IN (SELECT id FROM .... JOIN .... JOIN ... WHERE ...) But when I have a table with 100k+ records it become a big problem. Script just hangs up forever As I understand, mysql get each id from table `table` and reexamine SELECT? I tried to create temprary table and filled it with select like DELETE FROM table WHERE id IN (SELECT id FROM `some_temp_table`) But its hangs up too... EXPLAIN do not works for the DELETE ... statement, manual do not helped. So, how does this query work "on the low level"? DELETE FROM table WHERE id IN (SELECT id FROM .... JOIN .... JOIN ... WHERE ...) Any suggestion how can I improve speed for the large tables?
VER VERY VERY Slow MySQL Query HELP URGENTLY NEEDED
I have the following MySQL query, but it is VERY VERY slow and seems to be crashing the server. There are 300,000+ records in the 'tracker' table. SELECT sites.*, SUM(if(tracker.type='view',1,0)) AS numberOfViews, SUM(if(tracker.type='click',1,0)) AS numberOfClicks, SUM(tracker.revenue) AS totalRevenue FROM sites LEFT JOIN tracker ON tracker.site_id = sites.id GROUP BY sites.id ORDER BY sites.domain_name
Multiple WHERE X='$x' In A Query. (Quick Answer Needed)
My query works when I only use one WHERE instance, but not with both, and i know I have data that matches both criteria. Is this the proper way to pull results that match 2 criteria? $query = "SELECT * FROM Cartridges WHERE TYPE='$TYPE_Query' & PRINTER='$PRINTER_Query' ORDER BY ID ASC";
Help Needed With Load Data Infile Syntax
I'm relatively new to MySQL, and I've never used the Load Data Infile command. I'm having trouble getting it to work correctly. I have a comma separated file generated as output by another program that I can't seem to parse correctly. It contains data similar to the following: VAL1, LASTNAME, FIRSTNAME, ADDRESS1, ADDRESS2, ETC1, ETC2, ETC3 VAL2, "LASTNAME, JR.", FIRSTNAME, ADDRESS1, ADDRESS2, ETC1, ETC2, ETC3 The "LASTNAME, JR." hoses up the import. It imports "LASTNAME into one field and JR." into another.
Manual Cascade Delete Implementation Needed
I am trying to manual clean up a child table when parent table records are deleted. I tried: DELETE FROM agencyImage WHERE imageID IN (SELECT images.imageID FROM images LEFT OUTER JOIN agencyImage ON (images.imageID=agencyImage.imageID) WHERE agencyImage.imageID is null) This failed because you can't delete from a table that you are looking at. So how do I delete the record from agencyImage table if no record exist in the parent images table?
Advice Needed Login/registration System With MySQL 5/PHP
I want to setup a login/registration system for my website with MySQL 5 / PHP. Most of the job should be the standard stuff: register form, login form, forgot password form, etc. Can anyone advice me how to begin ? Are there any good tutorials which i should use or are there freeware or cheap commercial tools which i should use ? Or maybe you recommend me to an awesome programmer you know who does this job at low cost ? - a MySQL/PHP Newbie
Help Needed Fetching Data From Table (string Matching)
I have a table "officers" table two with 4 fields all with variable char which will end up just being names. However, I need to create a report that counts each the occurance of each name and ouputs the name with the count next to it. The names in table "officers" are concatinated from a first name field and last name field in a separate table "persons"; I need some help figuring out how to write this using PHP.
Does Mysql Store Max Size For Data Type Each Time, Or Only What Is Needed For Specific Data?
If I use MEDIUMTEXT, which (if I understand correctly) has a max size of 65k, does mysql use the entire size each time data is written to that column? I have a text field on my site that users fill in, but more often that not, they don't. However, when they do, it can be 1000 bytes or more. Am I wasting space using MEDIUMTEXT or does mysql only set aside 1000 bytes for that column if the data is only 1000 bytes?
Get Goals/assists For Each Game, And Player Name (was "Little Help Needed")
I have the following query: $result = @mysql_query("SELECT g.date , g.weeklabel , g.gmtime , g.home_score , g.away_score , g.inputted , th.teamname AS hometeam , th.id AS homeid , ta.teamname AS awayteam , ta.id AS awayid FROM weekly g JOIN teams th ON th.id = g.home_id JOIN teams ta ON ta.id = g.away_id ORDER BY g.date ASC, g.weeklabel, g.gmtime"); This works fine. However, I have another table called stats. The fields in this table are playerid, match_id, goals, assists. I have another table called league, with fields playerid, playername. I need to join the stats table to this query in order to get the goals/assists for each game, and I would also like to join the league table in order to get the players name instead of playerid. I have messed around with many different combinations and cant seem to get this particular query to work.
Models Where The Years Overlap (was "Query Help Needed")
I'm trying to formulate a query for the following example: Table: model_by_year model start_year end_year model_1 2001 2004 model_1 2003 2004 model_2 2000 2005 model_2 1998 1999 model_3 1998 2003 model_3 2002 2006 I want a query that will return all like models where the years overlap. In other words, I'd want to return model_1 and model_3, but not model_2.
Database Transfer Between Oracle 8 Database And MySQL Database
Is it somebody out there who has made a migration from an Oracle server to an MySQL server?? The scenario is as simply: I've got a Oracle 8 server with a database with content that I want to transfer to a MySQL database. No special data, constraints etc that MySQL not will handle. My solution is to reverse engineer the database from ERStudio and then produce a SQL script that will insert the data into the MySQL engine. But I can't do this, because the customer want me to do a PL/SQL script that does this, even if he never have worked with Oracle.. So the question is.. Do someone know a simplier way to do this or do I have to teach myself PL/SQL and make a script that produces a MySQL sql script ?
Script For Uploading Data From Local Database To Production Database
I am using MYSQL 4.0 for my local and production databases. I want to extract selected records from selected tables from local database and upload them to my production database on daily basis. In other words, I want to upload data which is entered in local database on daily basis to my production database.
|
TRACKER
