Asked  7 Months ago    Answers:  5   Viewed   31 times

Alright, so I'm fairly new to PHP and SQL/MySQL so any help is appreciated.

I feel like I took the right approach. I searched php.net for "MySQL show all table names", it returned a deprecated method and suggested using a MySQL query on SHOW TABLES [FROM db_name] [LIKE 'pattern'] I'm not sure what "pattern" means but, I searched for "SQL Wildcard" and got the "%" symbol. According to everything I found, this should work and output the table names at the end, but it does not. Any suggestions? Thanks in advance.

<?php
if ($_REQUEST["username"]=="coke"&&$_REQUEST["password"]=="pepsi"){
echo 'You have successfully logged in.';
echo '<br />';
echo 'These are your tables:';
echo '<br />';

   $link = mysql_connect("sql2.njit.edu", "username", "password");

   mysql_select_db("db_name") or die(mysql_error());

   $result = mysql_query('SHOW TABLES [FROM db_name] [LIKE '%']');
   echo $result;
}
else
echo 'You did not provide the proper authentication';
?>

I get no errors. The output is exactly what's echoed, but no table names.

 Answers

53

The square brackets in your code are used in the mysql documentation to indicate groups of optional parameters. They should not be in the actual query.

The only command you actually need is:

show tables;

If you want tables from a specific database, let's say the database "books", then it would be

show tables from books;

You only need the LIKE part if you want to find tables whose names match a certain pattern. e.g.,

show tables from books like '%book%';

would show you the names of tables that have "book" somewhere in the name.

Furthermore, just running the "show tables" query will not produce any output that you can see. SQL answers the query and then passes it to PHP, but you need to tell PHP to echo it to the page.

Since it sounds like you're very new to SQL, I'd recommend running the mysql client from the command line (or using phpmyadmin, if it's installed on your system). That way you can see the results of various queries without having to go through PHP's functions for sending queries and receiving results.

If you have to use PHP, here's a very simple demonstration. Try this code after connecting to your database:

$result = mysql_query("show tables"); // run the query and assign the result to $result
while($table = mysql_fetch_array($result)) { // go through each row that was returned in $result
    echo($table[0] . "<BR>");    // print the table that was returned on that row.
}
Wednesday, March 31, 2021
 
aurelijusv
answered 7 Months ago
39

You are confusing the filesystem path and web URL. You have to store only /biddingsystem/Images/ part or even just only name and generate full path dynamically at display time.

Also note that you aren't formatting your data properly, which will lead to some bugs and security vulnerabilities. I've explained formatting rules in my earlier answer, to Stack Overflow question How to include a PHP variable inside a MySQL insert statement.

Wednesday, March 31, 2021
 
Niels
answered 7 Months ago
74

From the PHP Manual on mysqli_stmt::execute:

mysqli_stmt::execute -- mysqli_stmt_executeExecutes a prepared Query

Returns TRUE on success or FALSE on failure.


if ($stmt->execute()) { // exactly like this!
    $success = true;
}

You're doing it right... What's your dilemma?

Wednesday, March 31, 2021
 
Sauleil
answered 7 Months ago
64

If you want to pull all tables with the values in mysql, you can use the following code:

<?php
    mysql_connect ("localhost", "DB_USER", "DB_PASSWORD"); //your mysql connection  
    mysql_select_db('DB_NAME') or die( "Unable to select database"); //your db name 

    $tables = mysql_query("SELECT table_name FROM information_schema.tables WHERE table_schema='DB_NAME'"); //pull tables from theh databsase
    while ($table= mysql_fetch_row($tables)) { 
        $rsFields = mysql_query("SHOW COLUMNS FROM ".$table[0]); 
        while ($field = mysql_fetch_assoc($rsFields)) { 
            echo $table[0].".".$field["Field"].", "; //prints out all columns 
        } 
        echo $table[0];
        $query = "SELECT * FROM ".$table[0]; //prints out tables name
        $result = mysql_query($query); 
        PullValues($result); //call function to get all values
    }  


    //function to pull all values from tables
    function PullValues($result) 
    {     
        if($result == 0) 
        { 
            echo "<b>Error ".mysql_errno().": ".mysql_error()."</b>"; 
        } 
        elseif (@mysql_num_rows($result) == 0) 
        { 
            echo("<b>Query completed. No results returned.</b><br>"); 
        } 
        else 
        { 
            echo "<table border='1'> 
                <thead> 
                <tr><th>[Num]</th>"; 
            for($i = 0;$i < mysql_num_fields($result);$i++) 
            { 
                echo "<th>" . $i . "&nbsp;-&nbsp;" . mysql_field_name($result, $i) . "</th>"; 
            } 
            echo "  </tr> 
                </thead> 
                <tbody>"; 
            for ($i = 0; $i < mysql_num_rows($result); $i++) 
            { 
                echo "<tr><td>[$i]</td>"; 
                $row = mysql_fetch_row($result); 
                for($j = 0;$j < mysql_num_fields($result);$j++)  
                { 
                    echo("<td>" . $row[$j] . "</td>"); 
                } 
                echo "</tr>"; 
            } 
            echo "</tbody> 
            </table>"; 
        }  //end else 
    }  
?>

I am using this and works fine in mysql, for mysqli you need to tweak it a very little.

Saturday, May 29, 2021
 
themihai
answered 5 Months ago
71

This is a FAQ for InnoDB tables. See the explanation at https://phpmyadmin.readthedocs.org/en/latest/faq.html?highlight=MaxExactCount#the-number-of-rows-for-innodb-tables-is-not-correct

Saturday, May 29, 2021
 
Student
answered 5 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :