SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Command to view tables in Mysql

    Hi,

    I there a command to see the list of tables under a particular database? I forgot the name of a table I had created.

    Thanks for the help.

  2. #2
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    show tables;
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  3. #3
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That was simple... thanks

  4. #4
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Need to know one more command. I need to change the property of a column of one of my tables. It should store varchar(50) instead of varchar(25) that it is storing now. What should the mysql command.

    Is there a place where I can get all the syntax in an easily searchable format. the mysql manual doesnt help me. I cannot find the info i am looking most of the time.

    Thanks for the help

  5. #5
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No problem, yes the docs on mysql.com can be pretty tricky at first, but they indeed have a logical flow, Look for the ALTER TABLE syntax section
    http://mysql.he.net/documentation/my...ml#ALTER_TABLE

    ALTER TABLE tablename modify columnname varchar(50);
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  6. #6
    You talkin to me? Anarchos's Avatar
    Join Date
    Oct 2000
    Location
    Austin, TX
    Posts
    1,438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    http://www.devshed.com/Server_Side/M...tro/page5.html has the info you need (it's a good tutorial as well).

  7. #7
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That was helpful. I think I will be able to find more stuff on my own now.

    Now, I have a problem in php which I cant figure out. I have a form which submits data to two tables. Its a form to add product to a catalog. the following are the columns of the products table ->id(auto increment), name, price and for prodlink table -> prodid, catid, color. I want to add the details first to the products table then take the id from there and add it to prodlink table along with the other details from the form. I am not sure how to retreive the id of the last inserted data in the products table since it is an auto_increment field.

    Any helpful would be appreciated.
    thanks

  8. #8
    You talkin to me? Anarchos's Avatar
    Join Date
    Oct 2000
    Location
    Austin, TX
    Posts
    1,438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select the row from the database using the information you using when inserting the row. For example:


    insert into blah values(null,"my name","my address","http://www.myhomepage.com";

    select * from blah where name="my name" and address="my address" and url="http://www.homepage.com";

  9. #9
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That would work but my table could have duplicate entries. Like the original table just has the name and the price of the product both of which could be same for 2 products with different colors (the color information I store in another table). In such a case the select query will give me more than one prodid which will mess up everything.

    Isn't there any other way of accessing the latest value or an auto_increment field or information about a recently added record.

    Thanks

  10. #10
    You talkin to me? Anarchos's Avatar
    Join Date
    Oct 2000
    Location
    Austin, TX
    Posts
    1,438
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah good point, you could do: SELECT * FROM blah ORDER BY id DESC LIMIT 1 or maybe SELECT * FROM blah WHERE id=MAX(id)

  11. #11
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats great. That should solve the problem.

    thanks.

  12. #12
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I tried what you suggested but for some reason it is returning the value which is one less than the max value. could it be that it is getting the max(id) before adding the new product??
    Following is the relevant code if it helps.
    PHP Code:

    if ($submit):

    $sql "INSERT INTO products SET " .
             
    "name='$ptitle', " .
             
    "price='$price', " .
             
    "quantity='$qty', " .
             
    "mprice='$mprice', " .
             
    "description='$desp', " .
             
    "image='$image'" ;

    $index=mysql_query("SELECT max(id) as id FROM products ");
    while (
    $index mysql_fetch_array($index)) {
      
    $id   $index["id"];
    }

    $sql1 "INSERT INTO products_categories SET " .
         
    "product_id='$id', " .
         
    "category_id='$catid', " .
         
    "shape_id='$shapeid', " .
         
    "size_id='$sizeid', " .
         
    "enhancement_id='$enhanceid'"
    Any help would be appreciated.
    Thanks.

  13. #13
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You can get the id number from your last insert statement provided you have an auto-incrementing id field using mysql_insert_id()

    PHP Code:
    if ($submit):
    $sql "INSERT INTO products SET " .
             
    "name='$ptitle', " .
             
    "price='$price', " .
             
    "quantity='$qty', " .
             
    "mprice='$mprice', " .
             
    "description='$desp', " .
             
    "image='$image'" ;

    $id mysql_insert_id($link_id);

    $sql1 "INSERT INTO products_categories SET " .
         
    "product_id='$id', " .
         
    "category_id='$catid', " .
         
    "shape_id='$shapeid', " .
         
    "size_id='$sizeid', " .
         
    "enhancement_id='$enhanceid'"
    where $link_id is the var you assigned your mysql_connect() to.
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  14. #14
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats a great functin to know. But for some reason it is returning 0 all the time. heres the code that I am implementing after your suggestion.

    PHP Code:

    if ($submit):

    $sql "INSERT INTO products SET " .
             
    "name='$ptitle', " .
             
    "price='$price', " .
             
    "quantity='$qty', " .
             
    "mprice='$mprice', " .
             
    "description='$desp', " .
             
    "image='$image'" ;

    $id mysql_insert_id($dbcnx);


    $sql1 "INSERT INTO products_categories SET " .
         
    "product_id='$id', " .
         
    "category_id='$catid', " .
         
    "shape_id='$shapeid', " .
         
    "size_id='$sizeid', " .
         
    "enhancement_id='$enhanceid'";
     
     if (
    mysql_query($sql) and mysql_query($sql1)) {
        echo(
    "<P>Product added</P>" $id);
      } else {
        echo(
    "<P>Error adding new product: " .
             
    mysql_error() . "</P>");
      } 
    $dbcnx is the mysql_connect() variable as you had said which i use as following

    PHP Code:
    $dbcnx = @mysql_connect("localhost""root""pass"); 
    Please tell me where I am getting wrong. ON querying the mysql through command line i get

    mysql> select * from products;
    +----+------+-------+----------+--------+-------------+-------+
    | id | name | price | quantity | mprice | description | image |
    +----+------+-------+----------+--------+-------------+-------+
    | 1 | | 0.00 | 0 | 0.00 | | |
    +----+------+-------+----------+--------+-------------+-------+
    1 row in set (0.05 sec)

    mysql> select * from products_categories;
    +------------+-------------+----------+---------+----------------+
    | product_id | category_id | shape_id | size_id | enhancement_id |
    +------------+-------------+----------+---------+----------------+
    | 0 | 9 | 34 | 4 | 1 |
    +------------+-------------+----------+---------+----------------+
    1 row in set (0.00 sec)

    and addition of 2nd products is dissallowed saying duplicate entry for primary key (product_id) which shows that id value is still 0.

    Please help...
    Thanks

  15. #15
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Because mysql_insert_id cant be run until the first query has been executed. Try this:

    PHP Code:
    if ($submit):

    $sql "INSERT INTO products SET " .
             
    "name='$ptitle', " .
             
    "price='$price', " .
             
    "quantity='$qty', " .
             
    "mprice='$mprice', " .
             
    "description='$desp', " .
             
    "image='$image'" ;
     if (
    mysql_query($sql)) {
        
    $id mysql_insert_id($dbcnx);
        echo(
    "<P>Product added</P>" $id);
      } else {
        echo(
    "<P>Error adding new product: " .
             
    mysql_error() . "</P>");
      }



    $sql1 "INSERT INTO products_categories SET " .
         
    "product_id='$id', " .
         
    "category_id='$catid', " .
         
    "shape_id='$shapeid', " .
         
    "size_id='$sizeid', " .
         
    "enhancement_id='$enhanceid'";
     
     if (
    mysql_query($sql1)) {
        echo(
    "<P>Product added</P>" $id);
      } else {
        echo(
    "<P>Error adding new product: " .
             
    mysql_error() . "</P>");
      } 
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  16. #16
    SitePoint Enthusiast tipiyanos's Avatar
    Join Date
    Jun 2001
    Location
    NY, New York
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Problem solved. Thanks for the help.

    I have another question for you. Is there any way I can ask the script to execute either both or none of the inserts. What i mean is that if for some reason the 2nd query doesnt get executed then the corresponding data in the products table added due to the first query doesnt make any sense. How can I stop this from happenning.

    I appreciate your time and help.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •