[RESOLVED] Adding a MySQL Variable to HTML via PHP

darkdestroyer

Hi All,

I am a realtively novice PHP/MySQL developer.
I have been baking my noodle for days now on this.
I have a login page that takes a username/password, queries it against a MySQL DB and returns a list of users.
I store the Username/Password as a variable, and then use it to pass as a parameter in various queries.

One of the functions of the site is that when a user logs in, he/she is presented with a list of associated users in their company.
They are then able to edit the details for that user.
This all works fine.

However, I am using a Form to Submit the data to MySQL, once the update has happened i have a simple set of links at the bottom of the page to navigate the user back to the user list page.

<html>
    <head>
        <title>PeopleSafe</title>

</head>
<body>                

<ul id="tabs1">
  <li><a href="contacts_list.html">Login Page</a></li>
  <li><a href="return_contacts_list.php?AccountID=<?php echo $AccountID;?>">User List</a></li>
  <li><a href="user_details.php?ContactID=<?php echo $ContactID;?>&AccountID=<?php echo $AccountID;?>">User Details</a></li>
  <li><a href="escalation_details.php?ContactID=<?php echo $ContactID;?>&AccountID=<?php echo $AccountID;?>">Escalation Details</a></li>
  <li><a href="pool_users.php?ContactID=<?php echo $ContactID;?>&AccountID=<?php echo $AccountID;?>">Pool Users</a></li>
</ul>

<?php
$_REQUEST["Pool01Name"];
$_REQUEST["Pool02Name"];
$_REQUEST["Pool03Name"];
$_REQUEST["Pool04Name"];
$_REQUEST["Pool05Name"];
$_REQUEST["Pool06Name"];
$_REQUEST["Pool07Name"];
$_REQUEST["Pool08Name"];
$_REQUEST["Pool09Name"];
$_REQUEST["Pool10Name"];
$_REQUEST["Pool11Name"];
$_REQUEST["Pool12Name"];
$_REQUEST["Pool13Name"];
$_REQUEST["Pool14Name"];
$_REQUEST["Pool15Name"];
$_REQUEST["Pool16Name"];
$_REQUEST["Pool17Name"];
$_REQUEST["Pool18Name"];
$_REQUEST["Pool19Name"];
$_REQUEST["Pool20Name"];
$_REQUEST["Pool01Number"];
$_REQUEST["Pool02Number"];
$_REQUEST["Pool03Number"];
$_REQUEST["Pool04Number"];
$_REQUEST["Pool05Number"];
$_REQUEST["Pool06Number"];
$_REQUEST["Pool07Number"];
$_REQUEST["Pool08Number"];
$_REQUEST["Pool09Number"];
$_REQUEST["Pool10Number"];
$_REQUEST["Pool11Number"];
$_REQUEST["Pool12Number"];
$_REQUEST["Pool13Number"];
$_REQUEST["Pool14Number"];
$_REQUEST["Pool15Number"];
$_REQUEST["Pool16Number"];
$_REQUEST["Pool17Number"];
$_REQUEST["Pool18Number"];
$_REQUEST["Pool19Number"];
$_REQUEST["Pool20Number"];
$_REQUEST["Pool01DOB"];
$_REQUEST["Pool02DOB"];
$_REQUEST["Pool03DOB"];
$_REQUEST["Pool04DOB"];
$_REQUEST["Pool05DOB"];
$_REQUEST["Pool06DOB"];
$_REQUEST["Pool07DOB"];
$_REQUEST["Pool08DOB"];
$_REQUEST["Pool09DOB"];
$_REQUEST["Pool10DOB"];
$_REQUEST["Pool11DOB"];
$_REQUEST["Pool12DOB"];
$_REQUEST["Pool13DOB"];
$_REQUEST["Pool14DOB"];
$_REQUEST["Pool15DOB"];
$_REQUEST["Pool16DOB"];
$_REQUEST["Pool17DOB"];
$_REQUEST["Pool18DOB"];
$_REQUEST["Pool19DOB"];
$_REQUEST["Pool20DOB"];
$_REQUEST["ContactID"];
$_REQUEST["AccountID"];
$_REQUEST["FirstName"];
$_REQUEST["LastName"];

$_FirstName = $_REQUEST["FirstName"];
$_LastName = $_REQUEST["LastName"];
$_AccountID = $_REQUEST["AccountID"];
$_ContactID = $_REQUEST["ContactID"];
$_Pool01Name = $_REQUEST["Pool01Name"];
$_Pool02Name = $_REQUEST["Pool02Name"];
$_Pool03Name = $_REQUEST["Pool03Name"];
$_Pool04Name = $_REQUEST["Pool04Name"];
$_Pool05Name = $_REQUEST["Pool05Name"];
$_Pool06Name = $_REQUEST["Pool06Name"];
$_Pool07Name = $_REQUEST["Pool07Name"];
$_Pool08Name = $_REQUEST["Pool08Name"];
$_Pool09Name = $_REQUEST["Pool09Name"];
$_Pool10Name = $_REQUEST["Pool10Name"];
$_Pool11Name = $_REQUEST["Pool11Name"];
$_Pool12Name = $_REQUEST["Pool12Name"];
$_Pool13Name = $_REQUEST["Pool13Name"];
$_Pool14Name = $_REQUEST["Pool14Name"];
$_Pool15Name = $_REQUEST["Pool15Name"];
$_Pool16Name = $_REQUEST["Pool16Name"];
$_Pool17Name = $_REQUEST["Pool17Name"];
$_Pool18Name = $_REQUEST["Pool18Name"];
$_Pool19Name = $_REQUEST["Pool19Name"];
$_Pool20Name = $_REQUEST["Pool20Name"];
$_Pool01Number = $_REQUEST["Pool01Number"];
$_Pool02Number = $_REQUEST["Pool02Number"];
$_Pool03Number = $_REQUEST["Pool03Number"];
$_Pool04Number = $_REQUEST["Pool04Number"];
$_Pool05Number = $_REQUEST["Pool05Number"];
$_Pool06Number = $_REQUEST["Pool06Number"];
$_Pool07Number = $_REQUEST["Pool07Number"];
$_Pool08Number = $_REQUEST["Pool08Number"];
$_Pool09Number = $_REQUEST["Pool09Number"];
$_Pool10Number = $_REQUEST["Pool10Number"];
$_Pool11Number = $_REQUEST["Pool11Number"];
$_Pool12Number = $_REQUEST["Pool12Number"];
$_Pool13Number = $_REQUEST["Pool13Number"];
$_Pool14Number = $_REQUEST["Pool14Number"];
$_Pool15Number = $_REQUEST["Pool15Number"];
$_Pool16Number = $_REQUEST["Pool16Number"];
$_Pool17Number = $_REQUEST["Pool17Number"];
$_Pool18Number = $_REQUEST["Pool18Number"];
$_Pool19Number = $_REQUEST["Pool19Number"];
$_Pool20Number = $_REQUEST["Pool20Number"];
$_Pool01DOB = $_REQUEST["Pool01DOB"];
$_Pool02DOB = $_REQUEST["Pool02DOB"];
$_Pool03DOB = $_REQUEST["Pool03DOB"];
$_Pool04DOB = $_REQUEST["Pool04DOB"];
$_Pool05DOB = $_REQUEST["Pool05DOB"];
$_Pool06DOB = $_REQUEST["Pool06DOB"];
$_Pool07DOB = $_REQUEST["Pool07DOB"];
$_Pool08DOB = $_REQUEST["Pool08DOB"];
$_Pool09DOB = $_REQUEST["Pool09DOB"];
$_Pool10DOB = $_REQUEST["Pool10DOB"];
$_Pool11DOB = $_REQUEST["Pool11DOB"];
$_Pool12DOB = $_REQUEST["Pool12DOB"];
$_Pool13DOB = $_REQUEST["Pool13DOB"];
$_Pool14DOB = $_REQUEST["Pool14DOB"];
$_Pool15DOB = $_REQUEST["Pool15DOB"];
$_Pool16DOB = $_REQUEST["Pool16DOB"];
$_Pool17DOB = $_REQUEST["Pool17DOB"];
$_Pool18DOB = $_REQUEST["Pool18DOB"];
$_Pool19DOB = $_REQUEST["Pool19DOB"];
$_Pool20DOB = $_REQUEST["Pool20DOB"];

$link = mysql_connect("localhost", "username","password") or die(mysql_error());
if (!$link) {
    die('Could not connect: ' . mysql_error());
}

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

$sql="UPDATE contacts SET 
Pool01Name='$_Pool01Name',
Pool02Name='$_Pool02Name',
Pool03Name='$_Pool03Name',
Pool04Name='$_Pool04Name',
Pool05Name='$_Pool05Name',
Pool06Name='$_Pool06Name',
Pool07Name='$_Pool07Name',
Pool08Name='$_Pool08Name',
Pool09Name='$_Pool09Name',
Pool10Name='$_Pool10Name',
Pool11Name='$_Pool11Name',
Pool12Name='$_Pool12Name',
Pool13Name='$_Pool13Name',
Pool14Name='$_Pool14Name',
Pool15Name='$_Pool15Name',
Pool16Name='$_Pool16Name',
Pool17Name='$_Pool17Name',
Pool18Name='$_Pool18Name',
Pool19Name='$_Pool19Name',
Pool20Name='$_Pool20Name',
Pool01Number='$_Pool01Number',
Pool02Number='$_Pool02Number',
Pool03Number='$_Pool03Number',
Pool04Number='$_Pool04Number',
Pool05Number='$_Pool05Number',
Pool06Number='$_Pool06Number',
Pool07Number='$_Pool07Number',
Pool08Number='$_Pool08Number',
Pool09Number='$_Pool09Number',
Pool10Number='$_Pool10Number',
Pool11Number='$_Pool11Number',
Pool12Number='$_Pool12Number',
Pool13Number='$_Pool13Number',
Pool14Number='$_Pool14Number',
Pool15Number='$_Pool15Number',
Pool16Number='$_Pool16Number',
Pool17Number='$_Pool17Number',
Pool18Number='$_Pool18Number',
Pool19Number='$_Pool19Number',
Pool20Number='$_Pool20Number',
Pool01DOB='$_Pool01DOB',
Pool02DOB='$_Pool02DOB',
Pool03DOB='$_Pool03DOB',
Pool04DOB='$_Pool04DOB',
Pool05DOB='$_Pool05DOB',
Pool06DOB='$_Pool06DOB',
Pool07DOB='$_Pool07DOB',
Pool08DOB='$_Pool08DOB',
Pool09DOB='$_Pool09DOB',
Pool10DOB='$_Pool10DOB',
Pool11DOB='$_Pool11DOB',
Pool12DOB='$_Pool12DOB',
Pool13DOB='$_Pool13DOB',
Pool14DOB='$_Pool14DOB',
Pool15DOB='$_Pool15DOB',
Pool16DOB='$_Pool16DOB',
Pool17DOB='$_Pool17DOB',
Pool18DOB='$_Pool18DOB',
Pool19DOB='$_Pool19DOB',
Pool20DOB='$_Pool20DOB'
WHERE ContactID='$_ContactID'";
  $query = mysql_query($sql);

if ($query) {

exit;
} 




mysql_close();

?>





<center><ul id="tabs">

  <a href="return_contacts_list.php?username=<?php echo $Account;?>&password=<?php echo $AccountID;?>">User List</a>
  <a href="user_details.php?ContactID=<?php echo $ContactID;?>&AccountID=<?php echo $AccountID;?>">User Details</a>
  <a href="escalation_details.php?ContactID=<?php echo $ContactID;?>&AccountID=<?php echo $AccountID;?>">Escalation Details</a>
  <a href="pool_users.php?ContactID=<?php echo $ContactID;?>&AccountID=<?php echo $AccountID;?>">Pool Users</a>
</ul>
<BR>
  <a href="logout.html">Logout</a>


</body>
</html>

The main issue is:
<a href="return_contacts_list.php?username=<?php echo $Account;?>&password=<?php echo $AccountID;?>">User List</a>

when i hover over the link it just shows /username=password=
it should show something like /username=joebloggspassword=GIYGUU67

This works fine on my other pages, its only on my UPDATE pages that it fails...
Could anyone point out what im doing wrong.

Sorry if that was long winded.
Thanks in advance.

bradgrafelman

Where do you ever define the variables $Account, $AccountID and $ContactID before you attempt to use them? If you'll turn on display_errors and set error_reporting to E_ALL, you'll probably find that PHP is trying to notify you that those are undefined variables (thus it should make sense why your links don't contain any information in place of those variables).

Furthermore... note that all of these statements:

$_REQUEST["Pool01Name"]; 
$_REQUEST["Pool02Name"]; 
$_REQUEST["Pool03Name"]; 
$_REQUEST["Pool04Name"]; 
$_REQUEST["Pool05Name"]; 
$_REQUEST["Pool06Name"]; 
$_REQUEST["Pool07Name"]; 
$_REQUEST["Pool08Name"]; 
$_REQUEST["Pool09Name"]; 
$_REQUEST["Pool10Name"]; 
$_REQUEST["Pool11Name"]; 
$_REQUEST["Pool12Name"]; 
$_REQUEST["Pool13Name"]; 
$_REQUEST["Pool14Name"]; 
$_REQUEST["Pool15Name"]; 
$_REQUEST["Pool16Name"]; 
$_REQUEST["Pool17Name"]; 
$_REQUEST["Pool18Name"]; 
$_REQUEST["Pool19Name"]; 
$_REQUEST["Pool20Name"]; 
$_REQUEST["Pool01Number"]; 
$_REQUEST["Pool02Number"]; 
$_REQUEST["Pool03Number"]; 
$_REQUEST["Pool04Number"]; 
$_REQUEST["Pool05Number"]; 
$_REQUEST["Pool06Number"]; 
$_REQUEST["Pool07Number"]; 
$_REQUEST["Pool08Number"]; 
$_REQUEST["Pool09Number"]; 
$_REQUEST["Pool10Number"]; 
$_REQUEST["Pool11Number"]; 
$_REQUEST["Pool12Number"]; 
$_REQUEST["Pool13Number"]; 
$_REQUEST["Pool14Number"]; 
$_REQUEST["Pool15Number"]; 
$_REQUEST["Pool16Number"]; 
$_REQUEST["Pool17Number"]; 
$_REQUEST["Pool18Number"]; 
$_REQUEST["Pool19Number"]; 
$_REQUEST["Pool20Number"]; 
$_REQUEST["Pool01DOB"]; 
$_REQUEST["Pool02DOB"]; 
$_REQUEST["Pool03DOB"]; 
$_REQUEST["Pool04DOB"]; 
$_REQUEST["Pool05DOB"]; 
$_REQUEST["Pool06DOB"]; 
$_REQUEST["Pool07DOB"]; 
$_REQUEST["Pool08DOB"]; 
$_REQUEST["Pool09DOB"]; 
$_REQUEST["Pool10DOB"]; 
$_REQUEST["Pool11DOB"]; 
$_REQUEST["Pool12DOB"]; 
$_REQUEST["Pool13DOB"]; 
$_REQUEST["Pool14DOB"]; 
$_REQUEST["Pool15DOB"]; 
$_REQUEST["Pool16DOB"]; 
$_REQUEST["Pool17DOB"]; 
$_REQUEST["Pool18DOB"]; 
$_REQUEST["Pool19DOB"]; 
$_REQUEST["Pool20DOB"]; 
$_REQUEST["ContactID"]; 
$_REQUEST["AccountID"]; 
$_REQUEST["FirstName"]; 
$_REQUEST["LastName"];

do absolutely nothing but waste space and potentially cause warnings to be emitted (if a given array index doesn't exist).

Furthermore, the fact that you have a bunch of numbered columns in your SQL database suggests that you probably need to read a few good articles on DB normalization (and then hopefully refactor your DB design).

Finally, note that you don't need those numbered fields in your HTML form, either. If you had elements named like so:

<input type="text" name="Pool[][Name]">
<input type="text" name="Pool[][Number]">
<input type="text" name="Pool[][DOB]">

<!-- repeat the above three sets of fields ad nasusem... ->

Then $_POST['Pool'] would be an array that looks something like:

Array
(
    [0] => Array
        (
            [0] => Name1
            [1] => Number1
            [2] => DOB1
        )

[1] => Array
    (
        [0] => Name2
        [1] => Number2
        [2] => DOB2
    )
# etc.
)

meaning you could use a simple [man]for/man or [man]foreach/man loop to process each tuple of information. (The benefits of doing this become even more apparent once you realize that after normalizing your DB schema, you'll want to insert each tuple as a new row - not combine multiple sets of them on a single row.)

Also note that you should never place user-supplied data directly into a SQL query string, else your code will be vulnerable to SQL injection attacks and/or just plain SQL errors. See [man]security.database.sql-injection[/man] for more information.

Finally, note that the entire [man]mysql[/man] extension is outdated and has been deprecated for some time now; the PHP manual discourages everyone from using it and recommends [man]MySQLi[/man] or [man]PDO[/man] as replacements. See [man]mysqlinfo.api.choosing[/man] for more information.

darkdestroyer

Hi Dan,

thanks for the detailed reply.

I thought that this statement:

$_REQUEST["ContactID"];
$_REQUEST["AccountID"]; 

$_AccountID = $_REQUEST["AccountID"];
$_ContactID = $_REQUEST["ContactID"];

would have declared the variables for use in the rest of the document?

On the second point about the list of statements:

$_REQUEST["Pool01Name"]; 
$_REQUEST["Pool02Name"]; 
$_REQUEST["Pool03Name"]; 
$_REQUEST["Pool04Name"]; 
$_REQUEST["Pool05Name"];

etc....

Are they not required to pull information from the text boxes on the previous page in order to pass them to the mysql insert?

Thanks for the comments on DB normalisation/Security, i am revisiting PHP/MySQL using code i had written in Uni many years ago!
I need a thorough brush up!

Thanks

bradgrafelman

darkdestroyer;11011779 wrote:
Hi Dan,

Who's Dan? 🙂

darkdestroyer;11011779 wrote:
I thought that this statement:
$_REQUEST["ContactID"];
$_REQUEST["AccountID"]; 

$_AccountID = $_REQUEST["AccountID"];
$_ContactID = $_REQUEST["ContactID"];
would have declared the variables for use in the rest of the document?

As I said before, statements like this:

$_REQUEST["ContactID"];
$_REQUEST["AccountID"];

do absolutely nothing. It's similar to all three of the following statements:

1+2;
FALSE;
;

in that you aren't telling PHP to actually do anything (which is why nothing gets done).

These statements, however:

$_AccountID = $_REQUEST["AccountID"];
$_ContactID = $_REQUEST["ContactID"];

do indeed define two variables - $AccountID and $ContactID - but you never actually use those variables anywhere. Meanwhile, $Account, $AccountID and $ContactID are still all left undefined.

darkdestroyer;11011779 wrote:
On the second point about the list of statements:
$_REQUEST["Pool01Name"]; 
$_REQUEST["Pool02Name"]; 
$_REQUEST["Pool03Name"]; 
$_REQUEST["Pool04Name"]; 
$_REQUEST["Pool05Name"]; 
etc....

Are they not required to pull information from the text boxes on the previous page in order to pass them to the mysql insert?

Again, none of those statements do anything, so they only thing for which they are required is to clutter up your code with superfluous lines that don't do anything (sans reducing the readability of your code and possibly generating error messages, of course).

Bonesnap

darkdestroyer;11011779 wrote:
Hi Dan,

thanks for the detailed reply.

I thought that this statement:
$_REQUEST["ContactID"];
$_REQUEST["AccountID"]; 

$_AccountID = $_REQUEST["AccountID"];
$_ContactID = $_REQUEST["ContactID"];
would have declared the variables for use in the rest of the document?

PHP doesn't require you to "declare" or "define" your variables before using them. At least not in the same way is you would in other languages (usually strongly typed languages).

The following two examples are identical and completely valid PHP syntax:

$x = 5;
echo $x;

//output: 5

$x;
$x = 5;
echo $x;

//output: 5

While the second example is technically valid syntax, as brad has noted, the first statement doesn't do anything.

With that said, your variables still have to exist before you can use them. You can check for that by using constructs like isset() and empty().

darkdestroyer

Hi Guys,

thanks for the replies, sorry Brad i havent a clue where i pulled the name Dan from... :bemused:

i see where i made my mistake now in the declaring the variables...

Thanks for the inputs, back to the books for me, its been about 8 years since i did any PHP/MySQL... and it shows...

Thanks