Inserting variables in mySQL table!

Collapse
This topic is closed.
X
X
 
  • Time
  • Show
Clear All
new posts
  • Raqueeb Hassan

    Inserting variables in mySQL table!

    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

    #2
    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.

    Comment

    • Raqueeb Hassan

      #3
      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
      Now, next, and beyond: Tracking need-to-know trends at the intersection of business and technology

      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

      Comment

      • Jeff North

        #4
        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/ph­p/200...hengin­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_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]

        Now, next, and beyond: Tracking need-to-know trends at the intersection of business and technology


        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
        ---------------------------------------------------------------

        Comment

        • Raqueeb Hassan

          #5
          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!

          Comment

          • Bill Karwin

            #6
            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.

            Comment

            • Raqueeb Hassan

              #7
              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

              Comment

              Working...