MySQL Newbie

First post, sorry if it's in the wrong section, and I've only been using MySQL for a few days, so I'm sure this is a dumb question.

I have a table that has only one entry per day, kind of a "picture of the day" thing. I have it all set so the table is full through the end of the year. I would like to display only the current dates image/description/name on the page. Here is my code:

<?php
require_once ('includes/config.inc');

require_once ('mysql_connect.php');

$query = "SELECT id, iname, date, description, name FROM upload WHERE date = 'CURDATE()'";
$result = @mysql_query ($query); //
$num = mysql_num_rows ($result);

if ($num == 1) {
$id = mysql_result($result,0,'id');
$iname = mysql_result($result,0,'iname');
$date = mysql_result($result,0,'date');
$description = mysql_result($result,0,'description');
$name = mysql_result($result,0,'name');



} else { // If it did not run OK.
echo '<p>I can't find that picture, please head back to my homepage! <a href="index.php">here</a>.</p>';
}

?>

I use the echo command to display the info further down the page. The query works when I swap out "CURDATE()" for "2008-12-01" or "2008-11-15" or any other date, however, I would like it to change automatically at midnight, and not have to do it manually by changing the query every day.

I have tried swapping out CURDATE() with CURRENT_DATE, and that does not work.

I have an existing table with a primary key (unsigned int, auto increment). I need to add a new field, and change the primary key to be that new field, instead. I am able to remove the auto increment spec from the field (using MySQL Administrator) but, when I try to remove the primary key status from the original field, the Drop Primary Key command results in Error Number 1025.

How can I change the primary key to be the new field?

Thanks in advance!

Hi,

i wrote the following line of code but whenever i execute, i get an error message "Fatal error encountered during command execution"

code:

' If the connection string is null, use a default.
If cnn = "" Then
cnn = ConfigurationManager.ConnectionStrings("equizdbConnectionString").ToString()
End If
Dim myConnection As New MySqlConnection(cnn)
Dim myInsertQuery As String '= "INSERT INTO equizDB (cid, customerId, amount) Values(1001, 23, 30.66)"
Dim fname As String = txtFirstname.Text
Dim sname As String = txtSurname.Text
Dim id As String = txtStaffId.Text
Dim random As New FastRandom()
Dim pCode As Integer = random.Next(11111, 99999) 'generates random
Dim branchName As String = ddBranch.Text
Dim div As String = ddDivision.Text
Dim grade As String = ddGrade.Text
Dim sector As String = ddSector.Text
Dim emailAddy As String = txtEmail.Text
Dim Ip As String = Request.UserHostAddress.ToString


myInsertQuery = "INSERT INTO cusers (cid,passcode,cfname,csname,branch,division,sector,email,grade,IP)VALUES (" _
& id & "," & pCode & "," & fname & "," & sname & "," & branchName & "," & div & "," & sector & "," _
& emailAddy & "," & grade & "," & Ip & ")"

Dim myCommand As New MySqlCommand(myInsertQuery)
If IsPostBack Then

Try
myCommand.Connection = myConnection
myConnection.Open()
'myConnection.CreateCommand()
myCommand.ExecuteNonQuery()

Response.Write("New user [ " & txtFirstname.Text & " ] successfully added to the database")

myCommand.Connection.Close()
Catch mySqlEx As MySqlException

Response.Write("ERROR! a problem occured while creating new user, contact system administrator ")
Response.Write(mySqlEx.Message)
Response.Write(mySqlEx.Number)
End Try
End If



am i missing sth? any help please. am new to MySql

Hi folks,


I have a /etc/postfix/mysql_*.cf file with following content user=mail password=mypasswd dbname=maildb table='t' select_field=domains where_field=domains hosts=127.0.0.1 additional_conditions = and enabled = 1
Please help me to understand table='t'. What is 't'?

TIA


Stephen L

Is it possible to use UNION ALL with Temporary tables? I keep getting an error saying: "Can't reopen table". If I remove the UNION ALL, I don't receive that error.

I'm trying to write a query to find the records with the highest value based off a ID

To simplify the table lets say it is song_id, user_id, date_completed, score

I'm trying to do something simliar to this to return the highest values for each song where the user_id is set to something.

SELECT `song_stats`.*, MAX(score) as max_score FROM `song_stats` WHERE `user_id` = '1' GROUP BY `song_id

The problem with this query is its not returning the actual record where the score is the highest, it is just placing a max_score field in addition to the first field it finds when it does the group by. So my problem is the date_completed field (and all the other fields not mentioned) is not accurate when I get the returned records.

How can I write something simliar to this, but return the EXACT full record of the highest score for each song?

hi,
This is Dheeraj Gupta, i want to fetch record from database as a paging system like google, so if some one have any sample code or any idea how to implement this pls tell me.

thanks to advance.
waiting for ur reply.
bye.

-- moved topic --

Hi Everyone,
I am new to use stored procedure in my sql, i have one query that is,
i have two diffrent procedure and i have created third procedure and i want to call these two procedure in my third procedure, so if any one have idea pls tell me.

i am also posting my procedure code:-

CREATE PROCEDURE `test`.`proc_tot_record`( OUT tot_record INT )

BEGIN
select count(*) into tot_record from colljobsheets;
END


CREATE PROCEDURE `test`.`paging_with_param`( IN _start Int, IN _end INT )
BEGIN
PREPARE STMT from "select COLLJS_JOBSERIAL_FK from colljobsheets LIMIT ?, ? ";
SET @start=_start;
SET @end=_end;
EXECUTE STMT USING @start, @end;
END


This is my third procedure which calling both of above procedure, pls tell me where i am going wrong to call this.
CREATE PROCEDURE `test`.`proc_with_param`(start INT, _end INT )

BEGIN
call paging_with_param(start,_end);
call proc_tot_record(@a);
Select @a;
END


pls help me out for this.
waiting for ur reply.
thanks.

Hi
i have proftpd on my server and the user is in the database koll and the table
ftpusers
how can i get out information abut when the custummer have loget on the last time
and also how can i se witch custummer thats have not been loged on the last 30days

Tnx for help

/tka

I used an lock before operation and unlock after this operation.It is inefficiency?i have to say.
Can the operations be in parallel when i already know that tables has no relationship?

Hey, I wasn't sure where to post this, so I'll do it in the newbie forums.


Basically I'm not very good at SQL, but I'm developing a project for tutorials on other subjects.

I have a number of tables, all with the prefix 'a_realm_'

OKay, so I'll just get to the heart of it.

I need to join my tutorials, user, categories, and ratings table. The ratings can be null, and that's where I'm running into issues.

Here's what I have so far:

SELECT t.url, t.title, c.cat_url, u.username AS author, u.username_clean AS author_clean, t.description, rate.num_ratings
FROM a_realm_tutorials t
JOIN a_realm_categories c on t.cat_id = c.category_id
JOIN a_realm_users u on u.id = t.author_id
JOIN

((

SELECT IF( `r`.`tutorial_id` IS NOT NULL , COUNT( * ) , 0 ) AS `num_ratings` FROM a_realm_tutorial_ratings r
JOIN a_realm_tutorials tt
on r.tutorial_id = tt.tut_id
GROUP BY tt.tut_id

)rate)

WHERE c.cat_url = 'basic_tutorials'

GROUP by t.tut_id


Ratings table has user_id, rating_id, rating_value (enum 1-5), tutorial_id


What happens is, everything gets pulled up fine and dandy, but if there's no entries in the ratings table, I get no results. If I enter ratings, all of the rate.num_ratings gets pulled up as just '1'. If I take out the entries.

I need an AVG in there somewhere too to average the rating.

please anyone help :(

Hi every one,
I am looking for a stand-alone MySQL query parser which can split the query into several parts, like where conditions, from tables, and so on.
I've searched on the web as much as I can, but what I got usable is a module written in Perl: MyParse, MyParsePP. I also have troubles with them, such as compiling errors and version compatible prolbems.
I know the MySQL parser works under the basis of lex and yacc, and I wonder if there is any way I can use them directly or, extract them from MySQL sources to get them work.
Any help is appreciated and thanks in advance~

I need a basic database for a class project. The professor is looking for us to find something on the web and alter it for our own. if anyone has a small program with a few queries that run I would greatly appreciate it if you could email me the file. I've been all over MySQL site and cant find anything so I figure the best option is to go to a forum. I'd be very grateful if someone would be willing to help me out. Thank you

Email: m_cgamer@yahoo.com

Hello,

I have too many logs files in my /var/log so i want to add "FLUSH LOGS" and "RESET MASTER" functions on a bash ? any ideas ? is it possible ?

Thanks of your answers.

-- moved topic --

My server recently broke and I had to reformat it. Being the stupid noob I am I only had a backup of the servers files and not the sql tables. I have all the original mysql files and I was wondering if there was a way of getting the data on to a new sql server.

Thanks.
Chris

Hi,

I'm having a problem while trying to create a trigger.
I want to look the column quantidade of table produto, if it is more then 1 I subtract this of 1, if not I do not permit the insertion.

I have tried this:

DROP TRIGGER IF EXISTS `tr_compra`;

Create trigger `tr_compra` before insert on `cliente_compra_produto` for each row
BEGIN
SET @qtd = 0;
SET @id = new.`id_produto`;
SELECT `p`.`quantidade` INTO @qtd
FROM `produto` `p`
WHERE `p`.`id_produto` = @id;
IF @qtd > 1 THEN
UPDATE `produto`
SET `quantidade` = @qtd - 1
where `id_produto` = @id;
ELSE
SELECT 'Quantidade insuficiente no estoque.'
ROLLBACK;
END IF;
END;

But I got syntax errors:
Script line: 3 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 3
Script line: 6 Unknown table 'new' in field list
Script line: 10 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 'IF @qtd > 1 THEN
UPDATE `produto`
SET `quantidade` = @qtd - 1
' at line 1



Can anyone help me?

Thanks,
Vanessa Sena

I can copy entire databases between two pcs (1 has MySQL 6 the other MySQL 5) just fine using...

mysqldump -u username_a -ppassword_a --all-databases | mysql -u username_b -ppassword_b -h host username_a - Should be the username for the server you are backing up password_a - Should be the password for the server you are backing up username_b - Should be the username for the new server password_b - Should be the password for the new server host - Should be the hostname or ip address of the new server
However I just want to copy 1 table in 1 database.

The help file shows:

There are three general ways to invoke mysqldump: shell> mysqldump [options] db_name [tables] shell> mysqldump [options] --databases db_name1 [db_name2 db_name3...] shell> mysqldump [options] --all-databases If you do not name any tables following db_name or if you use the --databases or --all-databases option, entire databases are dumped.
I've tried various versions of #1 ( themysqldump [options] db_name [tables]) but can't get it to work.
I've also tried the --tables

--tables Override the --databases or -B option. mysqldump regards all name arguments following the option as table names.
But couldn't get it to work either.

Anyone got a clue ?

>

Hi All
Can someone tell me if this is possible or do I need to wake up. I'm trying to delete some records from three different tables at once via check boxes, tabls don't have any join. some help will be greatley appreciated.
if($_POST['delete']) { foreach($_POST as $job_id) { mysql_query("DELETE FROM job_tb,blockBook,blockBook2 WHERE job_tb.job_id='$job_id' OR blockBook.job_id= '$job_id' OR blockBook2.job_id= '$job_id'"); if (mysql_affected_rows() > 0) { print "<font color=red size=2>Job No. = $Job_id has been deleted</font><p>"; } } } Thanks
Zed