By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,919 Members | 1,074 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,919 IT Pros & Developers. It's quick & easy.

Inserting variables in mySQL table!

P: n/a
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_id) VALUES
($word_id,$page_id);
ERROR 1054: Unknown column '$word_id' in 'field list'
Thanks in advance.

--
Raqueeb Hassan
Bangladesh

Jul 23 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Raqueeb Hassan wrote:
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_id) VALUES
($word_id,$page_id);
ERROR 1054: Unknown column '$word_id' in 'field list'


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.
Jul 23 '05 #2

P: n/a
> 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/ph*p/200...chengin*e.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_id) 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

Jul 23 '05 #3

P: n/a
On 28 Jun 2005 07:58:04 -0700, in mailing.database.mysql "Raqueeb
Hassan" <wi*******@gmail.com> wrote:
| > 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/ph*p/200...chengin*e.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_id) VALUES ($word_id,$page_id);
| <---- I couldn't do this! As it was generating that previous error.
| What might be the cause, please?
|
| -----


http://www.onlamp.com/pub/a/php/2002...ne.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_id) VALUES ('Bill',1);
INSERT INTO occurrence (word_id,page_id) VALUES ('Fred',2);
INSERT INTO occurrence (word_id,page_id) VALUES ('Sam', 3);
etc etc etc
---------------------------------------------------------------
jn******@yourpantsyahoo.com.au : Remove your pants to reply
---------------------------------------------------------------
Jul 23 '05 #4

P: n/a
> Simply put, the command line is trying to write these variables as
fixed information, and failing.


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!

Jul 23 '05 #5

P: n/a
Raqueeb Hassan wrote:
INSERT INTO occurrence (word_id,page_id) VALUES ($word_id,$page_id);
<---- I couldn't do this! As it was generating that previous error.
What might be the cause, please?


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.
Jul 23 '05 #6

P: n/a
> 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.


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

Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.