Inserting variables in mySQL table!

[Raqueeb Hassan]

Hello There!

Have you guys tried inserting variables in mySQL tables? Do I have to
use 'quote' as we had been doing to insert strings?

mysql> INSERT INTO occurrence (word_id,page_i d) VALUES
($word_id,$page _id);
ERROR 1054: Unknown column '$word_id' in 'field list'


Thanks in advance.

--
Raqueeb Hassan
Bangladesh

[Bill Karwin]

Re: Inserting variables in mySQL table!

Raqueeb Hassan wrote:[color=blue]
> Hello There!
>
> Have you guys tried inserting variables in mySQL tables? Do I have to
> use 'quote' as we had been doing to insert strings?
>
> mysql> INSERT INTO occurrence (word_id,page_i d) VALUES
> ($word_id,$page _id);
> ERROR 1054: Unknown column '$word_id' in 'field list'[/color]

There are no variables in the mysql tool identified by the $ symbol.
There is support in the mysql tool for user variables, using the @
symbol. See http://dev.mysql.com/doc/mysql/en/variables.html.

I'm inferring that you're using the mysql tool because of the "mysql>"
prompt you include in your example. If you're talking about using
variables in a perl or php script, then yes, you need to quote string
variables just as you would string literals. No need to quote values
for numeric fields.

Regards,
Bill K.

[Raqueeb Hassan]

Re: Inserting variables in mySQL table!

> There are no variables in the mysql tool identified by the $ symbol.[color=blue]
> There is support in the mysql tool for user variables, using the @
> symbol. See http://dev.mysql.com/doc/mysql/en/variables.html.[/color]

<snip>

Thanks for the reply, Mr. Bill. Basically I was using the idea from

http://www.onlamp.com/pub/a/php/2002/10/24/simplesearchengine.html

and
while inserting the values Author said ...

--------

When building the index, only three SQL INSERT statements actually
matter. When a page is first indexed, it must be recorded:


INSERT INTO page (page_url) VALUES ("http://www.onlamp.com/");
The first occurrence of a word within the entire dataset must be
recorded:


INSERT INTO word (word_word) VALUES ("linux");
Each occurrence of a word within a page must be recorded:


INSERT INTO occurrence (word_id,page_i d) VALUES ($word_id,$page _id);
<---- I couldn't do this! As it was generating that previous error.
What might be the cause, please?

-----


Thanks in advance.


Raqueeb Hassan
Bangladesh

[Jeff North]

Re: Inserting variables in mySQL table!

On 28 Jun 2005 07:58:04 -0700, in mailing.databas e.mysql "Raqueeb
Hassan" <wideangle@gmai l.com> wrote:
[color=blue]
>| > There are no variables in the mysql tool identified by the $ symbol.
>| > There is support in the mysql tool for user variables, using the @
>| > symbol. See http://dev.mysql.com/doc/mysql/en/variables.html.
>|
>| <snip>
>|
>| Thanks for the reply, Mr. Bill. Basically I was using the idea from
>| http://www.onlamp.com/pub/a/php/200...hengine.html
>| and
>| while inserting the values Author said ...
>|
>| --------
>|
>| When building the index, only three SQL INSERT statements actually
>| matter. When a page is first indexed, it must be recorded:
>|
>|
>| INSERT INTO page (page_url) VALUES ("http://www.onlamp.com/");
>| The first occurrence of a word within the entire dataset must be
>| recorded:
>|
>|
>| INSERT INTO word (word_word) VALUES ("linux");
>| Each occurrence of a word within a page must be recorded:
>|
>|
>| INSERT INTO occurrence (word_id,page_i d) VALUES ($word_id,$page _id);
>| <---- I couldn't do this! As it was generating that previous error.
>| What might be the cause, please?
>|
>| -----[/color]

http://www.onlamp.com/pub/a/php/2002/10/24/simplesearchengine.html?page=2

Are you using the populate.php sample file or the command line.
If you are using the command line syntax then you do not have the
variables $word_id or $page_id.

Simply put, the command line is trying to write these variables as
fixed information, and failing.

If you want to use the command line then you will need to enter
something like:
INSERT INTO occurrence (word_id,page_i d) VALUES ('Bill',1);
INSERT INTO occurrence (word_id,page_i d) VALUES ('Fred',2);
INSERT INTO occurrence (word_id,page_i d) VALUES ('Sam', 3);
etc etc etc
---------------------------------------------------------------
jnorthau@yourpa ntsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------

[Raqueeb Hassan]

Re: Inserting variables in mySQL table!

> Simply put, the command line is trying to write these variables as[color=blue]
> fixed information, and failing.[/color]

Thanks for the pointer, Jeff. Now, I get it, those were j-u-s-t
examples as fed by populate.php. And those $word_id,$page_ id needs to
be replaced by that php file as
http://localhost/populate.php?url=ht...devcenter.com/ sends it
back to mysql tables!

Thanks once again. You saved my hairs!


--
Raqueeb Hassan
Bangladesh


PS: Can you please point me more examples on creating simple search
engine? Thanks!

[Bill Karwin]

Re: Inserting variables in mySQL table!

Raqueeb Hassan wrote:[color=blue]
> INSERT INTO occurrence (word_id,page_i d) VALUES ($word_id,$page _id);
> <---- I couldn't do this! As it was generating that previous error.
> What might be the cause, please?[/color]

Variables with a leading $ are specific to PHP (or Perl, shell, or other
languages that use that kind of identifier). These identifiers have no
meaning to MySQL, but MySQL never sees the variables, only their values.

The technique being shown is to interpolate PHP variables into a string.
Then the string -- with the variables evaluated into their values --
is sent to MySQL to be executed as a SQL statement.

This technique depends on being run in a PHP environment, to do the
variable interpolation. If you are executing the statements in the
mysql command-line tool, you need to provide values literally, or with
the "@foo" type of user variables supplied in the mysql tool.

Regards,
Bill K.

[Raqueeb Hassan]

Re: Inserting variables in mySQL table!

> Variables with a leading $ are specific to PHP (or Perl, shell, or other[color=blue]
> languages that use that kind of identifier). These identifiers have no
> meaning to MySQL, but MySQL never sees the variables, only their values.[/color]

Thanks Bill. That's how php interpolate variables into a string and
send the values to mysql, as you said. I had been looking for similar
kind of so called "Google Desktop Search SDK", which would index my
drives (windows/linux partitions) with php script to mysql as SWISH-e.

To simply put, I would like to have mysql as backend to a php script
which would index everything like SWISH-e.

Thanks in advance.

--
Raqueeb Hassan
Bangladesh

--
The hardest part about moving forward is not looking back. - Sally