Conditional Mysql Insert query

Here are the structure of two tables in my current Mysql/ PHP project:


CREATE TABLE urimask_main
(
link_id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(link_id),

target_url TEXT NOT NULL,

url_md5 CHAR(32) NOT NULL,

display_url VARCHAR(255) NOT NULL,

redirection_type SMALLINT NOT NULL DEFAULT \\'301\\',

UNIQUE(display_url)

)


CREATE TABLE urimask_alias
(
link_id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(link_id),

target_url TEXT NOT NULL,

url_md5 CHAR(32) NOT NULL,

display_url VARCHAR(255) NOT NULL,

redirection_type SMALLINT NOT NULL DEFAULT \\'301\\',

UNIQUE(display_url)
)


New data gets added to both urimask_main and uirmask_alias tables via forms.
The display_url field must never contain duplicate values within the tables nor can urimask_main and uirmask_alias ever have the value for display_url.

In other words, if adding new data to urimask_alias then the value of urimask_alias.display_url must be unduplicated within the urimask_alias table AND not exist in the urimask_main.display_url column.

Here are different codes I have tried:


<?php
require_once('config.php');
//note undefined functions and UPPERCASE constants used in this example are defined in the config.php file

$connection_to_urimask = connect_db(USERNAME,PASSWORD, SERVER);

$target_url = 'http://google.com';

$url_md5 = md5($target_url );

$alias = 'search-engine';

$redirection_type = '301';

$query[0] = "
INSERT INTO urimask_alias
(urimask_alias.target_url, urimask_alias.url_md5, urimask_alias.display_url, urimask_alias.redirection_type)
VALUES
('$target_url', '$url_md5', (SELECT urimask_main.display_url FROM urimask_main RIGHT JOIN urimask_alias ON urimask_main.display_url = $alias WHERE urimask_main.display_url IS NULL),'$redirection_type')
";

$query [1] = "
INSERT INTO urimask_alias
(urimask_alias.target_url, urimask_alias.url_md5, urimask_alias.display_url, urimask_alias.redirection_type)
SELECT $target_url, $url_md5, $alias, $redirection_type FROM urimask_main RIGHT JOIN urimask_alias ON urimask_main.display_url = $alias WHERE urimask_main.display_url IS NULL
";

/*
from config.php
function query_db($dbname, $connection, $db_query)
	{
		mysql_select_db($dbname, $connection);
		//execute to create db query
		$results = mysql_query($db_query, $connection);

			
		return $results;
			
	}

*/


//executes query
query_db(DBNAME, $connection_to_urimask, $query[0]);

echo '<pre>'; var_dump($query[0], mysql_affected_rows()); 

query_db(DBNAME, $connection_to_urimask, $query[1]);

echo '<pre>'; var_dump($query[1], mysql_affected_rows()); 

?>

Both $query[0] and $query[1] failed.

What is the proper to write an conditional mysql insert under this scenario?

Also would it have been better to redesign this project so that the field display_url was in separate table, with unique values, that joined with urimask_main and urimask_alias?

Refactor to just have one table, and if display_url is completely unique, then consider using it as the primary key instead of creating a numeric PK link_id.

Else, can you explain why you need two tables, because I cannot spot the reason.

Thanks for the reply Cups.

Refactor to just have one table, and if display_url is completely unique, then consider using it as the primary key instead of creating a numeric PK link_id.

Else, can you explain why you need two tables, because I cannot spot the reason.

Let me preface my response to your question by saying that I am a MYSQL newbie. Thus I am learning as I go along. That being said, this is all part of a personal link shortener / redirection PHP script like Bit.ly or Tinyurl.com.

The main function of the script is to input a target urls like this post’s url: ‘http://www.sitepoint.com/forums/php-34/conditional-mysql-insert-query-773566.html’ and output a short, redirection display url like “http://example.com/c”.

The reasons for two tables is because they work differently and have different functions. Urimask_main automatically assigns a sequential, alphanumeric display urls to each input target url.
Urimask_alias lets you decide what the display url will be for each target url.

I wanted avoid collisions where a display url appeared in both tables and have different target urls.

Anyways, I figured out a solution by creating a temp table that mirrors urimask_alias:



<?php
require_once('config.php');
//note undefined functions and UPPERCASE constants used in this example are defined in the config.php file

$connection_to_urimask = connect_db(USERNAME,PASSWORD, SERVER);

$target_url = 'http://google.com';

$url_md5 = md5($target_url );

$alias = 'search-engine';

$redirection_type = '301';

$urimask_alias_temp = '
CREATE TEMPORARY TABLE urimask_alias_temp
(
link_id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(link_id),

target_url TEXT NOT NULL,

url_md5 CHAR(32) NOT NULL,

display_url VARCHAR(255) NOT NULL,

redirection_type SMALLINT NOT NULL DEFAULT \\'301\\',

UNIQUE(display_url)
)';


$urimask_alias_temp_insert = "
INSERT INTO urimask_alias_temp
(urimask_alias_temp.target_url, urimask_alias_temp.url_md5, urimask_alias_temp.display_url, urimask_alias_temp.redirection_type)
VALUES ('$target_url', '$url_md5', '$alias','$redirection_type')
";


$query = "INSERT INTO urimask_alias
(urimask_alias.target_url, urimask_alias.url_md5, urimask_alias.display_url, urimask_alias.redirection_type)
SELECT urimask_alias_temp.target_url, urimask_alias_temp.url_md5, urimask_alias_temp.display_url, urimask_alias_temp.redirection_type
FROM urimask_alias_temp LEFT JOIN urimask_main ON urimask_alias_temp.display_url = urimask_main.display_url WHERE urimask_alias_temp.display_url = '$alias' AND urimask_main.display_url IS NULL
";

//create temp table
query_db(DBNAME, $connection_to_urimask, $urimask_alias_temp);
//echo '<pre>'; var_dump($urimask_alias_temp); exit;

//insert into temp table
query_db(DBNAME, $connection_to_urimask, $urimask_alias_temp_insert);
echo '<pre>'; var_dump($urimask_alias_temp_insert, mysql_affected_rows()); //exit;

//;insert into alias table
query_db(DBNAME, $connection_to_urimask, $query);
echo '<pre>'; var_dump($query, mysql_affected_rows()); exit;

mysql_close($connection_to_urimask);

?>