Problem with mysql query results

PHP
1 
$stem_words=array_unique($stem_words);
        //array unique bezan inja
         foreach ($stem_words as $word) {
             $sql="SELECT * FROM `inverted_index`.`inverted_index` WHERE wordname='".$word."'";
             $res=mysqli_query($connection, $sql);
              mysqli_error($connection);

             if (mysqli_num_rows($res)==1) {
                 $sql="INSERT INTO `inverted_index`.`inverted_index` (`wordname`, `word_in_duc`, `duc_names`) VALUES ('".$word."', '1','".$docname."')";
             $res=mysqli_query($connection, $sql);
             echo mysqli_error($connection);
             }else{
                $sql="UPDATE `inverted_index`.`inverted_index` SET word_in_duc=word_in_duc+1 , duc_names=CONCAT(duc_names,'".$docname."') WHERE wordname='".$word."'";
                $res=mysqli_query($connection, $sql);
                echo mysqli_error($connection);

             }

             
         }

hi
i have array that i used foreach to check all of this values AS $word so i use query if $word exist in database update this row and if not insert this row w=,but didnt worked,below you can see my table
;

CREATE TABLE `inverted_index` (

  `wordname` varchar(20) NOT NULL,

  `word_in_duc` int(5) NOT NULL,

  `duc_names` text NOT NULL,

  PRIMARY KEY (`wordname`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1
2

What’s the reason for specifying the table name twice in the queries?

$sql="SELECT * FROM `inverted_index`.`inverted_index` WHERE wordname='".$word."'";

And, I think your result check is the wrong way around. This line

if (mysqli_num_rows($res)==1) {
$sql="INSERT INTO `inverted_index`.`inverted_index` (`wordname`, `word_in_duc`, `duc_names`) VALUES ('".$word."', '1','".$docname."')";

says that when you’ve executed the SELECT query, if you get one result you want to insert a new row. Surely that would be the code for if you get zero results? If you get one result, then you need to do the UPDATE query.

I’d also think that rather than building up a comma-separated list of documents that the word appears in for your duc_names column, you might be better with another table containing wordname and docname, with multiple rows when a word appears in more than one document. Then your queries can deal with putting it all together, without having to make that a potentially massive column width if you have 10,000 documents and they all contain the word “the”.

1 Like
3

first one is the name of database

4

OK. So maybe it’s the result thing I mentioned - you want to insert if you get zero results back from the SELECT, otherwise UPDATE. If that doesn’t help, can you clarify what “doesn’t work” means exactly?

1 Like
5

if word exist update it if not so update the word details,

i cant understand this one you told me

6

i changed if (mysqli_num_rows($res)==0) and now every thing is works fine,

@droopsnoot now i wana create table for thing that you sayed,

thanks for your valuable time

7

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.