explode a string and add to database

[richardjh]

Hello again

I have create a simple script which will add a string of tagwords to a mysql database table . This works fine but I want to also do the following with the tagword string...

1) I want to exlode the string (e.g this-is-a-string) into an array of separate words without the dash between)(e.g

Array

(

[0] => this

[1] => is

[2] => a

[3] => string

)

 

Then I want to check another mysql table to see if any of these words have a row. The ones that DON't have a row in the table I want to add them and auto increment the 'id'. If the DO have a row I simply want to increment the 'id'.

 

Could some one give me some idea how to do this please?

 

I have tried but got as far as 'explode' and then couldn't work out how to query the database table etc.

 

thanks for any help.

 

R

[Ken2k7]

Use a SELECT SQL to query the database to check if a word is in the table. If not, add it via SQL INSERT. If id is set to AUTO_INCREMENT, you don't have to worry about that column. It will increment itself.

[richardjh]

But how would I form the if statement to query the database with the information in the Array?

[aschk]

You need 2 things here.

1) An auto-increment column

2) A "count" column to keep a record of the number of occurences of this word.

This is how I would probably do it.

 

// My String to work with.
$mystring = "this-is-my-string";

// My String as an array.
$values = explode("-", $mystring);

// The SQL Query value for my string.
$query_value = "'" . implode("','", $values) . "'";

// SQL query.
$query = "SELECT * FROM <another-mysql-table> WHERE <column-name> IN (%s)";

// Execute Query.
$result = mysql_query(sprintf($query, $query_value));

// Create data array.
$data = array();
while($row = mysql_fetch_assoc($result)){
  $data[] = $row[<column-name>];
}

// Insert new values.
$new = array_diff($values, $data);
$query_value = "('" . implode("'),("',$new) . "')";
$result = mysql_query(sprintf("INSERT INTO <another-mysql-table>(<column-name>) VALUES %s", $query_value));

// Update old count values by 1.
$old = array_intersect($values, $data);
$query_value = "(". implode("'),('", $old) . ")";
$result = mysql_query(sprintf("UPDATE <another-mysql-table> SET count = count+1 WHERE <column-name> IN  %s", $query_value));

 

Please note, in the above code, you will have to create a MySQL connection yourself, and input the name of the table you want to check. You will also probably want to escape the values, which is something I have not done in my script. And you will probably want to sanitize this code for yourself as it is untested at present.

 

 

[richardjh]

Excellent thanks for that. I've added your code aschk and although the first table still updates the 2nd table (which this added code is for) still doesn't add or update. This is what I have so far:

 

$mystring = $taglist;

// My String as an array.
$values = explode("-", $mystring);

// The SQL Query value for my string.
$query_value = "'" . implode("','", $values) . "'";

// SQL query.
$query = "SELECT * FROM tags WHERE tag IN (%s)";

// Execute Query.
$result = mysql_query(sprintf($query, $query_value));

// Create data array.
$data = array();
while($row = mysql_fetch_assoc($result)){
  $data[] = $row['tag'];
}

// Insert new values.
$new = array_diff($values, $data);
$query_value = "(" . implode("'),('",$new) . "')";
$result = mysql_query(sprintf("INSERT INTO tags(tag) VALUES %s", $query_value));

// Update old count values by 1.
$old = array_intersect($values, $data);
$query_value = "(". implode("'),('", $old) . "')";
$result = mysql_query(sprintf("UPDATE tags SET count = count+1 WHERE tag IN  %s", $query_value));

// end

 

the 2nd table is called 'tags' and it has three columns - 'id'(auto increment) 'tag' and 'count'. The variable '$taglist' is the list of words the users has imput via a one line text box. The $taglist is added to the FIRST table successfully in the correct format (i.e the-string-of-tags) but so far the 2nd table remains unchanged.

 

There are a few lines in your code aschk that i don't understand simply because I'm a novice php'er. If when we can get this bit of code to work correctly I will ask you what the various bits mean e'g'

$query = "SELECT * FROM tags WHERE tag IN (%s)";

what does %s mean?

 

thanks for your valued help.

R

[killah]

He is using sprintf

 

$query = "SELECT * FROM tags WHERE tag IN (%s)";

$result = mysql_query(sprintf(QUERY HERE, ARGUMENT being %s));

 

%s => the argument/value is treated as and presented as a string.

[richardjh]

ok!

However I'm still trying to make this code work. Like I said I get no errors and table 1 updates and/or inserts correctly. I just can't seem to communicate with table 2 using this code.

 

I'll post the full code of what I have:

function Update($sid, $taglist)  {
include("../cfig.php");

// sanitize $taglist
$taglist = mysql_real_escape_string($taglist);
$taglist = strtolower($taglist); // coverts string to lowercase
$taglist = trim($taglist); // trims white space
$taglist = str_replace(", ", "-", $taglist); // replaces space in between words with a dash
$taglist = str_replace(" ", "+", $taglist); // replaces space in between words with a dash
$taglist = str_replace("'", "", $taglist); // replaces space in between words with a dash

// aschk's code start 
// My String to work with.
$mystring = $taglist;

// My String as an array.
$values = explode("-", $mystring);

// The SQL Query value for my string.
$query_value = "'" . implode("','", $values) . "'";

// SQL query.
$query = "SELECT * FROM tags WHERE tag IN (%s)";

// Execute Query.
$result = mysql_query(sprintf($query, $query_value));

// Create data array.
$data = array();
while($row = mysql_fetch_assoc($result)){
  $data[] = $row['tag'];
}

// Insert new values.
$new = array_diff($values, $data);
$query_value = "(" . implode("'),('",$new) . "')";
$result = mysql_query(sprintf("INSERT INTO tags(tag) VALUES %s", $query_value));

// Update old count values by 1.
$old = array_intersect($values, $data);
$query_value = "(". implode("'),('", $old) . "')";
$result = mysql_query(sprintf("UPDATE tags SET count = count+1 WHERE tag IN  %s", $query_value));

// aschk's code end

// code for update/insert of 1st tag table called (tags2)
$add_taglinks = "SELECT * FROM tags2 WHERE sid='$sid'";
$add_result = mysql_query($add_taglinks) or die(mysql_error());

if(mysql_num_rows($add_result) < 1) {  
    $result = mysql_query("insert into tags2 values ('', '$sid', '$taglist')", $db);
    
    if(!$result) {
    	echo (mysql_error());
    	
    		} else {
  echo "<p><center><b>Thank You.</b><br>The Tags are now in place for this book <a href=\"/expand.php?sid=$sid\">HERE</a><p>";
}
  } else {


$query2 = "UPDATE tags2 SET tag_list='$taglist' WHERE sid='$sid'";
$result2 = mysql_query($query2) or die(mysql_error());
  echo "<p><center><b>Thank You.</b><br>The Tag List has been updated for this book <a href=\"/expand.php?sid=$sid\">HERE</a><p>";
}
  }

 

Again thanks for the help and advice

R

[richardjh]

I've been pottering and I decided to echo the output for the above code and this is what I get:

If I add the tag words... 'this, is, a, string' (no surrounding quotes) I get as the echo:

(this'),('is'),('a'),('string')  (') 

 

Should this be the expected output? and what about the first word - it lacks an beginning apostrophe? Should this output be surrounded by brackets and/or appostrophes?

 

The query needs to be just the four words (in this case) which are checked and added to the tags2 table OR (if they already exist in the tags2 table) incremented by +1

 

hope someone can help

 

R

[richardjh]

I have a feeling it's something to do with this line

$query_value = "(" . implode("'),('",$new) . "')";

[richardjh]

Can anyone offer any advice on this please?