[Help inserting data into table]

Any fields that will potentially be left blank should be declared as NULL i.e.:

 

CREATE TABLE `people` (
  `id` int(3) NOT NULL auto_increment,
  `name` var(50) NOT NULL.
  `type` varchar(100) NOT NULL,
  `location` varchar(255) NULL DEFAULT NULL
);

 

The following insert would set location to NULL and id to the next auto_increment value:

 

INSERT INTO people (name, type) VALUES ('Albert', 'Cool');

 

NOTE: I am pretty sure that varchar has a max length of 255 - varchar(500) is likely to be trimmed anyway but is also misleading.

[SELECT Question]

or WHERE TRUE

[SQL conversion]

From my already limited understanding, at least one of the three joins needs to be available.

[[SOLVED] MYSQL PAUSE or PHP Wait Statement]

Consider using a join, something like:

 

DELETE customer_tb, customer_site_notes 
FROM customer_tb
     LEFT JOIN customer_site_notes ON customer_tb.id = customer_site_notes.id
WHERE customer_tb.name = '$delcust';

 

Use an INNER JOIN with an enforced 1:1 relationship between customer_tb and customer_site_notes.

[double left join causing confusion]

Consider altering the table definition if at all possible:

 

CREATE TABLE member (
     id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(100) NOT NULL DEFAULT 'undefined'
);

CREATE TABLE subscription (
     id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
     member_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
     start_date DATE NOT NULL DEFAULT '0000-00-00',
     end_date DATE NULL DEFAULT NULL,
     INDEX FK_MEMBER_ID (member_id)
);
[code]

Then something like this would probably be wrong:

[code]
SELECT member.id, member.name, latest_subscription.start_date, latest_subscription.end_date
FROM member
     INNER JOIN ( SELECT * FROM subscription ORDER BY start_date DESC ) AS latest_subscription ON member.id = member_id
GROUP BY member.id;

 

Where as this would be just wacky:

 

SELECT member.id, member.name, latest_subscription.start_date, latest_subscription.end_date
FROM member
     INNER JOIN ( 
          SELECT * FROM subscription INNER JOIN ( 
               SELECT member_id, max(start_date) AS max_start_date FROM subscription GROUP BY member_id 
          ) AS max_subscription ON subscription.member_id = max_subscription.member_id AND subscription.start_date = max_subscription.max_start_date
     ) AS latest_subscription ON member.id = latest_subscription.member_id;

[/code][/code]

[How "Alter" work on changing the type of Null ?]

ALTER TABLE 
     MODIFY advance_call_details varchar(240) NOT NULL DEFAULT 'undefined',
     MODIFY claims_summary text NOT NULL 

 

FYI: blob/text can not have a default value

[How to partiton a table having 80 million records]

Have a good long hard read and perhaps double read from the following link (I recommend a table for each day):

 

http://dev.mysql.com/doc/refman/4.1/en/merge-storage-engine.html

 

NOTE: I have never needed to and therefore never used The MERGE Storage Engine.

[How "Alter" work on changing the type of Null ?]

DEFAULT 'indefined' is the default value assigned to the column if none is provided and COMMENT 'advance call details' is like adding comments in programming code but the comment here is associated to the column and should describe the purpose of the information stored in the column.

[[SOLVED] Visual Basic 2005 and mysql]

Any documentation on using mysql via an odbc may be useful, maybe start your seach there:

 

http://dev.mysql.com/doc/refman/4.1/en/myodbc-connector.html

[How "Alter" work on changing the type of Null ?]

ALTER COLUMN is used only to modify the default value of a column.

 

The entire column definition is required to update the NULL/NOT NULL value of a column, as follows:

 

ALTER TABLE MODIFY advance_call_details varchar(240) NOT NULL DEFAULT 'undefined' COMMENT 'advances call details'

 

[MYSQL PHP Select question]

Assuming that DB1 and DB2 are not actually databases and refer to tables, I do not see any SQL that joins DB1 and DB2, something like:

 

SELECT d1.*, d2.citynameinenglish
FROM DB1 d1, DB2 d2
WHERE d1.cityid = d2.cityid

OR

SELECT d1.*, d2.citynameinenglish
FROM DB1 d1
     INNER JOIN DB2 d2 ON d1.cityid = d2.cityid

 

FYI: db2 should be d2 in the SELECT clause of your query, boko605.

[where to put the TRIGGER CODE ???]

I do not think MySQL triggers fire on a particular date, only when there are changes applied to the database - create a cronjob or windows task event to apply changes to a database on a particular day.

[[SOLVED] SMF problem with limit]

What happens when you add backticks to the date column?

 

$articles = db_query("
		SELECT `date`, author, subject, body, id
		FROM {$db_prefix}tp_articles 
		ORDER BY date DESC LIMIT 0,2", __FILE__,__LINE__);

[[SOLVED] Securing posted form data before storing in database (function not working)]

I performed some tests that produced some interesting results:

<?php
for ($i = 0; $i < 500000; $i++)
{
     $_POST['value'] = " ";

     preg_match('/^\s*$/', $_POST['value']) ? $_POST['value'] = "value" : $_POST['value'] = $_POST['value'];
}

SCRIPT END - Script Execution Time: 1.4779 Seconds 
SCRIPT END - Script Execution Time: 1.4797 Seconds 
SCRIPT END - Script Execution Time: 1.4729 Seconds 

for ($i = 0; $i < 500000; $i++)
{
     $_POST['value'] = " ";

     $_POST['value'] = preg_match('/^\s*$/', $_POST['value']) ? "value" : $_POST['value'];
}

SCRIPT END - Script Execution Time: 1.4364 Seconds
SCRIPT END - Script Execution Time: 1.4353 Seconds  
SCRIPT END - Script Execution Time: 1.4458 Seconds

for ($i = 0; $i < 500000; $i++)
{
     $_POST['value'] = " ";

     if (preg_match('/^\s*$/', $_POST['value'])) // TRUE
     {
          $_POST['value'] = "value";
     }
     else
     {
          $_POST['value'] = $_POST['value'];
     }	
}

SCRIPT END - Script Execution Time: 1.3963 Seconds 
SCRIPT END - Script Execution Time: 1.4059 Seconds 
SCRIPT END - Script Execution Time: 1.4021 Seconds 


for ($i = 0; $i < 500000; $i++)
{
     $_POST['value'] = " ";

     if (preg_match('/^\s*$/', $_POST['value'])) // TRUE
     {
          $_POST['value'] = $_POST['value'];
     }
}


SCRIPT END - Script Execution Time: 1.4746 Seconds 
SCRIPT END - Script Execution Time: 1.4774 Seconds 
SCRIPT END - Script Execution Time: 1.4808 Seconds 
?>

 

The results for the last two tests are quiet contradictory, could someone please verify the last two tests!! - The test result suggests that adding a superfluous else to an if would decrease the processing time.

 

I may have to stop using ternary operators so often.

[[SOLVED] Securing posted form data before storing in database (function not working)]

yeah empty($value) && value != 0 would be fine but I would write value != 0 first considering the empty condition depends on the outcome.

 

I often use  <condition> ? <true statement>  : <false statement> and always assumed that the syntax was just shorthand (I must of read that somewhere).

[[SOLVED] insert where]

Hmmm... an INSERT....SELECT might work:

 

INSERT INTO images (name, size, type, path) 
     SELECT $fileName, $fileSize, $fileType, $filePath FROM images 
     WHERE id = $bandid

 

 

[Not a numeric key field]

An index on a blob/text requires a fixed length:

 

ALTER TABLE table_name ADD UNIQUE UNIQUE_COLUMN_NAME (column_name(100)); # The index key is fixed to the length 100

[[SOLVED] Securing posted form data before storing in database (function not working)]

There is a limitation if only one value out of the entire array requires html entities to be converted.

 

... and use get_magic_quotes_gpc internally, if that is the only condition used for stipslashes.

 

I prefer prepareing post data for database insertion in a couple of seperate steps.

 

First just lightly clean up the data

 

foreach ($_POST as $key => $item) $_POST[$key] = trim( get_magic_quotes_gpc() ? stripslashes($item) : $item );

 

... and then the more complex type validation that includes string quoting or returns NULL;

 

mysql_query($query = "INSERT INTO table SET value = ".to_string($_POST['value']);

// return an escaped and quoted (if required html entity converted) value or null
function to_string($value, $htmlentities=false)
{
     return is_null($value) || empty($value) ? "NULL" : "'".mysql_real_escape_string( $htmlentities ? htmlentities($value) : $value )."'";
}

 

NOTE: The above function can not be used to convert numbers since empty(0) returns true.

[[SOLVED] SMF problem with limit]

Make this change:

 

$articles = db_query($query = "
		SELECT date, author, subject, body, id
		FROM {$db_prefix}tp_articles 
		ORDER BY date DESC LIMIT 0,2", __FILE__,__LINE__);

print '<PRE>'.$query.'</PRE>';

 

And post the query here.

[[SOLVED] Semi-Complex Query]

SELECT network.nid
    , network.userid
    , network.friendid
    , if (network.userid = 1, friend.username, owner.username) AS username
    , if (network.userid = 1, friend.main_image, owner.main_image) AS main_image
    , if (network.userid = 1, friend.level, owner.level) AS level
FROM network
    INNER JOIN users AS owner ON network.userid = owner.userid
    INNER JOIN users AS friend ON network.friendid = friend.userid 
WHERE ( network.userid = 1 OR network.friendid = 1 ) AND ap = 1;

 

SELECT network.nid
    , network.userid
    , network.friendid
    , if (network.userid = $userid, friend.username, owner.username) AS username
    , if (network.userid = $userid, friend.main_image, owner.main_image) AS main_image
    , if (network.userid = $userid, friend.level, owner.level) AS level
FROM network
    INNER JOIN users AS owner ON network.userid = owner.userid
    INNER JOIN users AS friend ON network.friendid = friend.userid 
WHERE ( network.userid = $userid OR network.friendid = $userid ) AND ap = $ap;

 

EDIT: though the query looks a lot different the primary problem was network.userid in the second LEFT JOIN should be network.friendid and I figured an INNER JOIN is more appropriate since both ids are required.

[SELECT Question]

Consider something like this:

 

mysql_query($query = "SELECT * FROM table".( $_POST['select'] != "Any" ? " WHERE row = '".$_POST['select']."'" : null ));

 

[SELECT Question]

To get all the rows of a table simply use:

 

SELECT * FROM table

[Default value to store semi static variable]

Found the usage:

 

ALTER TABLE reimbursement ALTER COLUMN rate SET DEFAULT .55

 

But the entire table is affected.

 

I do not understand why, especially since the above query is so explicit.

[a really skewed-up SQL statement]

How are you going to know what groups to add to each row?

 

INSERT...SELECT

[[SOLVED] A Really easy project, but need help]

$result = mysql_query($query = "SELECT timediff(time_allowed, time_used) as time_diff FROM scheduled_time WHERE scheduled_time.id = 1");
$record = mysql_fetch_assoc($result);
print $record['time_diff'];