Jump to content


  • Posts

  • Joined

  • Last visited


Everything posted by bubblegum.anarchy

  1. This seems a little to obvious to be what you are after: SELECT * # SELECT ALL THE COLUMNS FROM temp # FROM ALL THE RECORDS WHERE id2 = 0 # WHERE id2 = 0 ORDER BY lastdate DESC # SORTED BY lastdate DESC 1 0 Bye 2007-04-14 07:49:31 30Hello2007-04-12 23:47:26 20Hello2007-04-12 23:27:50
  2. Do you get the same result with mysql_query()?
  3. Definately a good start... let's see what others will suggest.
  4. A default value is inserted into a column for a record when the column value in the insert statement is not specificied... say you have a table definition such as this: CREATE TABLE table_name ( id INT(10) UNSIGNED NOT NULL, info VARCHAR(255) NOT NULL DEFAULT 'My shoe is blue' )TYPE=MyISAM; when you perform an insert like this: INSERT INTO table_name SET id = 1; and then SELECT * FROM table_name would result in: id info 1 My shoe is blue
  5. Depends on the application and how large the database may be... for starters you can save a little = space by defining `gender` as char(1) - M or F and you should also add an index on members_id in members_address, and also consider adding some default values for all those not nulls.
  6. grace5 - your create statement appears to be missing key/index definitions....is a index on `description` really necessary? consider removing the index on `description` (removing the index may have adverse effects): ALTER TABLE jade_sites DROP INDEX description, MODIFY description TEXT NOT NULL;
  7. I am not sure but I think default values apply only during inserts... consider this: DELETE FROM table_name WHERE user_id = $id; INSERT INTO table_name SET user_id = $id; The default values should be set during the insert so make sure all your column definitions have appropriate default values.
  8. SELECT *, max(id2) FROM temp GROUP BY id ORDER BY lastdate DESC; forgot the sort order.
  9. Should give you something like this... but always verify queries on test data! id id2 comment lastdate max(id2) 11Hi there2007-04-12 23:46:331 30Hello2007-04-12 23:47:260 21Nutton2007-04-12 23:50:291 sorry DanDaBeginner I did not realise you were asking the question (thought the original poster was)... yeah.. that should be right, group all the records by id and return only the max id2 which is what dabip wants... then order the result by lastdate... pretty sure that is what dabip wants, but I could only understand what dabip was after with your response... still, I am not 100% sure.
  10. SELECT *, max(id2) FROM temp GROUP BY id ORDER BY lastdate;
  11. I have never found quoting necessary but there usage is probably good coding practice.
  12. well, whatever the table name is is probably a word already used by MySQL.
  13. MySQL might be complaining if you had a table named table since table is a reserved word in MySQL, so quoting is required so MySQL is not trapped in a land of indecision.
  14. ... also, unlikely, but make sure you are not comparing against case sensative fields that are in lower case and make sure that the value held in $code is what you are expecting.
  15. The strtotime function was what the database values were designed for but I am now required to have the conversion performed directly in the sql query - I will have to try and find the code for the PHP strtotime function if an answer is not posted.
  16. This can not possible the best way to get what you want but... DROP TABLE IF EXISTS hockey; CREATE TABLE hockey ( submitid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, flames TEXT NULL, oilers TEXT NULL, canucks TEXT NULL, PRIMARY KEY (submitid) ) TYPE=MyISAM; INSERT INTO hockey SET flames = 'Iginla', oilers = 'Smith', canucks = ''; INSERT INTO hockey SET flames = '', oilers = 'Roloson', canucks = 'Sedin'; # ------------------------------------------------------------------------------------------------------------ CREATE TABLE IF NOT EXISTS hockey_flames SELECT flames FROM hockey ORDER BY flames DESC; ALTER TABLE hockey_flames ADD id int(10) unsigned not null auto_increment primary key; CREATE TABLE IF NOT EXISTS hockey_oilers SELECT oilers FROM hockey ORDER BY oilers DESC; ALTER TABLE hockey_oilers ADD id int(10) unsigned not null auto_increment primary key; CREATE TABLE IF NOT EXISTS hockey_canucks SELECT canucks FROM hockey ORDER BY canucks DESC; ALTER TABLE hockey_canucks ADD id int(10) unsigned not null auto_increment primary key; DELETE FROM hockey; ALTER TABLE hockey AUTO_INCREMENT = 1; INSERT INTO hockey (flames, oilers, canucks) SELECT hockey_flames.flames , hockey_oilers.oilers , hockey_canucks.canucks FROM hockey_flames INNER JOIN hockey_oilers ON hockey_flames.id = hockey_oilers.id INNER JOIN hockey_canucks ON hockey_flames.id = hockey_canucks.id; DROP TABLE hockey_flames; DROP TABLE hockey_oilers; DROP TABLE hockey_canucks; # ------------------------------------------------------------------------------------------------------------ SELECT * FROM hockey; Be sure to make a backup copy of the `hockey` table... I might spend a little more time to have the queries appear more elegant.
  17. You have an ERROR 1146: Table 'pronet2.1' doesn't exist and a table named pronet2 - update your query so that the table you reference is pronet2 rather than pronet2.1
  18. You have $variable[0] and $variable[1] the wrong way around... but why are your foreaching the newteamrole post value? anyway try this instead: list($person, $newrole) = explode(" ", $entry);
  19. Convert all HTML form data into the appropriate data type... especially string to numbers where they apply - consider incorporating the php settype function in your HTML post data type validation. When using something like "SELECT * FROM table WHERE id = ".$_GET['id'] the $_GET['id'] value should be wrapped in a type conversion so you would have something more like "SELECT * FROM table WHERE id = ".ToNumber($_GET['id']) and the ToNumber() function would incorporate the PHP settype function to restrict anyone replace the url query string value to an query UPDATE statement - any string will be returned as 0 with the settype function, simple and effective.
  20. field names and database security have little to do with one another.
  21. Hi Everyone! Here is what I am trying to do (using mysql only): Convert the following strings: Second Monday March Second Monday June First Tuesday November Last Thursday August to their respective mysql dates. SET @date_string := 'Second Monday March'; SELECT MYSQLCODEGENUIS(@date_string);
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.