[grouping records]

Lets say that you have a table with two columns: x and y

 

You pull a query:

 

SELECT x, y FROM table GROUP BY x

 

This will only return one record for each value of x, nut my question is which record does it use for the value of y? And can you select which record is used to return y using an ordering rule?

The first record for each unique `x` group is returned if no group by function is applied to `y`, but apparently there is no guarantee of this happening.

[[SOLVED] resource id issue]

remove the semicolon in the query string.

[Mysqlimport and LOAD DATA FILE]

Now the problem is this, i had to move my site to a new server, and the new server runs php in safe mode, so i'm able to run the mysqldump but i'm not able to run the mysql < dumpfile because the "<" is not permited... dont know why...

I thought `<` is a linux piping command, nothing to do with php - contact your server admin.

[[SOLVED] SHOW GRANTS]

use mysql;
select * from user;

 

Makes sense not to be able to list all the users without permission to the mysql table.

[SELECT command denied to user ''@'localhost' for table ...]

if the test connection was succesful then you can be assured there is an issue with the connection string that is responsible for the connection used by the page producing the error... like artacus initially deduced.

[SELECT command denied to user ''@'localhost' for table ...]

are you sure you are not connecting as an anonymous user that has no permissions?

 

create a basic connection file on the server for testing like this, straight out of the php manual, to verify the connection settings:

<?php
// Connecting, selecting database
$link = mysql_connect('mysql_host', 'mysql_user', 'mysql_password')
   or die('Could not connect: ' . mysql_error());
echo 'Connected successfully';
mysql_select_db('my_database') or die('Could not select database');

// Performing SQL query
$query = 'SELECT * FROM my_table';
$result = mysql_query($query) or die('Query failed: ' . mysql_error());

// Printing results in HTML
echo "<table>\n";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   echo "\t<tr>\n";
   foreach ($line as $col_value) {
       echo "\t\t<td>$col_value</td>\n";
   }
   echo "\t</tr>\n";
}
echo "</table>\n";

// Free resultset
mysql_free_result($result);

// Closing connection
mysql_close($link);
?>

[Need Help on PHP and MySQL wedding]

I do not know much about free web hosting... try a google search or search these forums for a previous topic related to free hosting.

[Noobie: Connecting to MySQL]

The connection details required should be available from the database host, in this case, yahoo - check the faq at yahoo?

[[SOLVED] how to query age.. between 18y/o and 22y/o]

I suppose extract(year from birthdate) would be faster than round(datediff(CURRENT_DATE, birthdate) / 365) and if the following is correct than extracting the date is substantially quicker:

DROP TABLE IF EXISTS birthdate;

CREATE TABLE birthdate (
id int(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
birthdate DATE NOT NULL 
)TYPE=MYISAM;

INSERT INTO birthdate SET birthdate = '1974-12-03';
INSERT INTO birthdate (birthdate) SELECT adddate(birthdate, 1) FROM birthdate; # repeated a number of times

SELECT count(*) FROM birthdate; # records created 2097152

SELECT count(round(datediff(CURRENT_DATE, birthdate) / 365)) AS test FROM birthdate; # average around 922 ms

SELECT count(extract(year from birthdate)) AS test FROM birthdate; # average around 328 ms

[Need Help on PHP and MySQL wedding]

notepad was commenting on the server setup

 

I work from home with WIMP (windows, IIS, mysql, php) and upload to a work server that is LAMP (linux, apache, mysql, php) both old versions of php and mysql so I am no help.. a google search on windows IIS php mysql install instructions should provide plenty of helpful information.

 

Just make sure what ever versions of php and mysql you install on your local machine for testing is the same as what is available on the production server!!

[[SOLVED] Order by statement]

This query seems to work:

SELECT * FROM table_name ORDER BY id = 5 desc, id

[[SOLVED] ORDER BY name ASC - but 10 is before 1 (please help)]

Extract the chapter and part number to create decimal values

 

Walkthrough Chapter 2: A Red Letter Day - Part 3

Walkthrough Chapter 3: Route Kanal - Part 1

Walkthrough Chapter 3: Route Kanal - Part 2

Walkthrough Chapter 30: Route Kanal - Part 2

 

would be

2.3

3.1

3.2

30.2

 

cast over to a float and sort as normal.... that would work, right?

[SELECT command denied to user ''@'localhost' for table ...]

Check the grant permissions for the username.

[Need Help on PHP and MySQL wedding]

heh.. I do not actually know of particular tutorials from experience but noticed this site has a few links from the front page to tutorials... try those!

[Help: How to increment a data]

a cronjob that runs the following query every hour

 

INSERT INTO temperature SELECT currentTemp + 1, MinTemp, MaxTemp, Time FROM temperature ORDER BY id DESC LIMIT 1;

[[SOLVED] how to query age.. between 18y/o and 22y/o]

oh ok - that is the part I misunderstood.. so you will still need WHERE round(datediff(CURRENT_DATE, birthdate) / 365) BETWEEN $fAge AND $tAge

[[SOLVED] how to query age.. between 18y/o and 22y/o]

I am actually not sure what you are asking but this should calculate the age based on a birthdate

SELECT round(datediff(CURRENT_DATE, birthdate) / 365) AS age FROM dating_profile;

[Need Help on PHP and MySQL wedding]

MySQL and php are free server technologies unlike windows and access and possibly the reason they are a cheaper server alternative... spend some time going over tutorials here and you should have no troubles migrating to mysql and php.

[CSV import into MYSQL]

verify all the data before inserting a single record... SQLyog has an import from csv feature.

[[SOLVED] REGEXP problem in mysql]

well... I can't figure a way around this problem without creating a seperate function.

[SQL Import File Error]

Rename the column or wrap the column name with backticks - ie.  `show`

[[SOLVED] SQL N00b with syntax issues]

have you tried removing PRIMARY or adding the word KEY

 

and consider changing

userSignupDate datetime NOT NULL default="now()",

to

userSignupDate timestamp NOT NULL default timestamp,

 

remove all the = signs and define the primary key either in the column definition or the statement footer

 

[[SOLVED] REGEXP problem in mysql]

I can't find anyway to get a match count with REGEXP.... hmm.

[[SOLVED] REGEXP problem in mysql]

are the numbers stored: number and semicolon? or just number, then semicolon and number... though you could always concat a semicolon to all the fields if the later is evident and then search for number and semicolon.

[count number of instances in last 30 days]

SELECT * 
FROM mytable 
WHERE mydate > subdate(now(), 30)
GROUP BY clli;