[[SOLVED] SELECT DISTINCT How to Use?]

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

[[SOLVED] PHP Variable in MySQL Query Problem]

`#` is a comment character.

[SELECT all - Index problems]

Do you get the same result with mysql_query()?

[The best way to set up a database table]

Definately a good start... let's see what others will suggest.

[The best way to set up a database table]

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

[The best way to set up a database table]

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.

[#1170 key specification without a key length]

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;

 

[[SOLVED] Set all to default]

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.

[[SOLVED] SELECT DISTINCT How to Use?]

SELECT *, max(id2) FROM temp GROUP BY id ORDER BY lastdate DESC;

 

forgot the sort order.

[[SOLVED] SELECT DISTINCT How to Use?]

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.

[[SOLVED] SELECT DISTINCT How to Use?]

SELECT *, max(id2) FROM temp GROUP BY id ORDER BY lastdate;

[Query `syntax` or just syntax ?]

I have never found quoting necessary but there usage is probably good coding practice.

[Query `syntax` or just syntax ?]

well, whatever the table name is is probably a word already used by MySQL.

[Query `syntax` or just syntax ?]

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.

[[SOLVED] Having a few SELECT issues...]

... 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.

[Date Extraction]

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.

 

[Lets say I have this:]

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.

[[localhost] ERROR 1146: Table 'pronet2.1' doesn't exist]

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

[[localhost] ERROR 1146: Table 'pronet2.1' doesn't exist]

Get the community version of SQLyog.

[[SOLVED] Updating data from multiple rows]

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);

 

[The best way to set up a database table]

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.

[The best way to set up a database table]

field names and database security have little to do with one another.

[Date Extraction]

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);