Displaying all table names in php from MySQL database

Question 1

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.

Question 2

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

Question 3

Try:

array_push($labels, $row['DATE'] );
array_push($temperature, $row['TEMPERATURE']);
array_push($humidity, $row['HUMIDITY']);

Looks like all you column names are upper case because of $conn->setAttribute(PDO::ATTR_CASE, PDO::CASE_UPPER);.

Question 4

While the main problem is that you missed the closing bracket after bookamandheading, still I would like to advise you to refactor this request for example like this:

$keys = array("bookabandheading", "bookaband", "contactus", "aboutuslisten",
              "contactusheading", "nightclubsheading", "acousticheading",
              "schoolsheading", "privateheading", "concertsheading",
              "festivalsheading", "submissions", "interns", "managementbio",
              "latestnews", "artistofthemonth", "artistofthemonthphoto",
              "artistofthemonthid", "listentoourartists", "musicianswanted",
              "aboutus", "bshowcases", "bandavails");
$set = array();
foreach ($keys as $key) {
    $set[] = sprintf(" %s = '%s' ", $key, mysql_escape_string($_POST[$key]));
}
$sql = mysql_query("UPDATE general SET " . implode(", ", $set));

It is much easier to maintain and also a bit more secure by escaping the input.

Update: add where statement example

$where = array();
$where[] = sprintf(" some_string = '%s' ", mysql_escape_string($some_string));
$where[] = sprintf(" some_integer = %d ", $some_integer);
$where = " WHERE " . implode(" AND ", $where);
$sql = mysql_query("UPDATE general SET " . implode(", ", $set) . " " . $where);

Question 5

You should use strtotime PHP function to convert string date to UNIX timestamp format and change MySQL data type for date field to TIMESTAMP.

Than you can do effective queries with > and <.

Question 6

I believe you are looking for the following syntax:

INSERT INTO <table> (field1, field2, field3, ...) 
VALUES ('value1', 'value2','value3', ...)
ON DUPLICATE KEY UPDATE
field1='value1', field2='value2', field3='value3', ...

Note: With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row, 2 if an existing row is updated, and 0 if an existing row is set to its current values.

MySQL Documentation: INSERT ... ON DUPLICATE KEY UPDATE Statement

Displaying all table names in php from MySQL database

Answers

php,mysql,sql