Pulling data from database

[distant]

I'm having a real hard time constructing a table that pulls out the data from the database.

Can anyone help please?

This is what I got:

 

<?php

$con = mysql_connect("localhost","gracom1_student","gra2143!");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

?>

 

<?php

include('connect.php');

mysql_select_db("gracom1_widgets", $con) or die(mysql_error());

$result = mysql_query("SELECT * FROM customers") or die(mysql_error());

 

mysql_select_db("my_db", $con);

 

$result = mysql_query("SELECT * FROM customers");

 

echo "<table width='600px'>

  <tr>

    <th>Customer Name</td>

    <th>Phone</td>

    <th>Email</td>

<th>Details</td> 

  </tr>";

 

while($row = mysql_fetch_array($result))

  {

  echo "<tr>";

  echo "<td>" . $row['Customer Name'] . "</td>";

  echo "<td>" . $row['Phone'] . "</td>";

  echo "<td>" . $row['Email'] . "</td>";

  echo "<td>" . $row['Details'] . "</td>";

  echo "</tr>";

  }

echo "</table>";

 

mysql_close($con);

 

?>

 

[premiso]

mysql_select_db("gracom1_widgets", $con) or die(mysql_error());
$result = mysql_query("SELECT * FROM customers") or die(mysql_error());

mysql_select_db("my_db", $con);

$result = mysql_query("SELECT * FROM customers");

 

Not sure if that is intended or not, but you have the same query for two separate tables, except that the second table is taking priority as it overwrites $result. Perhaps you need to remove the bottom select_db and query and it will work?

[distant]

Thanks for replying premisso.

I just did that bu still no data in my table other than the th tags

[premiso]

Just a question, are you sure you have data inside of your MySQL database?

 

I modified your code a bit, namely I changed the query to pull specific data, if you know what you need pull that out. Secondly I do not think MySQL allows for a space inside of a column name, maybe it does if you use back ticks to initiate it, either way the below should work. Part of the problem may have been your column names were being returned all lower case so when you tried accessing them in the associative array it saw  customer name as being different than Customer Name (like it should). So by defining the columns you wanted to pull out, we were also able to set the case of the column names too, so this "should" work as long as you have data in your database and the column names are what you were trying to pull.

 

<?php
include('connect.php');
mysql_select_db("gracom1_widgets", $con) or die(mysql_error());
$result = mysql_query("SELECT `Customer Name`, Phone, Email, Details FROM customers") or die(mysql_error());

echo "<table width='600px'>
  <tr>
    <th>Customer Name</td>
    <th>Phone</td>
    <th>Email</td>
    <th>Details</td> 
  </tr>";

while($row = mysql_fetch_assoc($result)) // notice I changed this as well. array feches both, you only need one
  {
  echo "<tr>";
  echo "<td>" . $row['Customer Name'] . "</td>";
  echo "<td>" . $row['Phone'] . "</td>";
  echo "<td>" . $row['Email'] . "</td>";
  echo "<td>" . $row['Details'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);

?>

 

(I am assuming the mysql_connect part you posted is housed inside of connect.php, if not remember to put that back in)

[distant]

Thanks premisso! You're helping me a lot.

Yes, the mysql_connect part you posted is housed inside of connect.php.

The table is working. However, been struggling with this evil thing but can't seem to figure out how to link the "Show" in the 4th column to another php page (that will have a small table with the details of each individual customer)?

And also how can I link the email in the third column to outlook (having the address on the "To" space in Outlook)?

I would really appreciate some help...

This is what I got so far:

 

<?php

$con = mysql_connect("localhost","gracom1_student","gra2143!");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

?>

 

 

<?php

include('connect.php');

mysql_select_db("gracom1_widgets", $con) or die(mysql_error());

$result = mysql_query("SELECT `custName`, custPhone, custEmail, custID FROM customers") or die(mysql_error());

 

echo "<table width='600px'>

  <tr>

    <th>Customer Name</td>

    <th>Phone</td>

    <th>Email</td>

    <th>Details</td>

  </tr>";

 

while($row = mysql_fetch_assoc($result))

  {

  echo "<tr>";

  echo "<td>" . $row['custName'] . "</td>";

  echo "<td>" . $row['custPhone'] . "</td>";

  echo "<td>" . $row['custEmail'] . "</td>";

  echo "<td>Show</td>";

  echo "</tr>";

  }

echo "</table>";

 

mysql_close($con);

 

?>

 

[premiso]

 <?php
include('connect.php');
mysql_select_db("gracom1_widgets", $con) or die(mysql_error());
$result = mysql_query("SELECT `custName`, custPhone, custEmail, custID FROM customers") or die(mysql_error());

echo "<table width='600px'>
  <tr>
    <th>Customer Name</td>
    <th>Phone</td>
    <th>Email</td>
    <th>Details</td>
  </tr>";

while($row = mysql_fetch_assoc($result))
  {
  echo "<tr>";
  echo "<td>" . $row['custName'] . "</td>";
  echo "<td>" . $row['custPhone'] . "</td>";
  echo '<td><a href="mailto:' . $row['custEmail'] . '?subject=someSubject">' . $row['custEmail'] . "</a></td>";
  echo '<td><a href="page.php?custID=' . $row['custID'] . '">Show</a></td>';
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);

?>

 

And for future reference, please code inside of

.

[distant]

Wow thanks premisso you rule!

One last thing. How can I make it so it shows only the selected row (and not the whole table)? I know it has something to do with the $_GET Function in the URL as I've been reading. Just can't seem to figure it out.

Can't thank you enough.

 

 

 <?php
include('connect.php');
mysql_select_db("gracom1_widgets", $con) or die(mysql_error());
$result = mysql_query("SELECT `custName`, custPhone, custEmail, custID FROM customers") or die(mysql_error());

echo "<table width='600px'>
  <tr>
    <th>Customer Name</th>
    <th>Phone</th>
    <th>Email</th>
    <th>Details</th> 
  </tr>";

while($row = mysql_fetch_assoc($result))
  {
  echo "<tr>";
  echo "<td>" . $row['custName'] . "</td>";
  echo "<td>" . $row['custPhone'] . "</td>";
  echo '<td><a href="mailto:' . $row['custEmail'] . '?subject=">' . $row['custEmail'] . "</a></td>";
  echo '<td><a href="details.php?custID=' . $row['custID'] . '">Show</a></td>';
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);

?>

[distant]

Wrong code. This is the right one:

 

 <?php
include('connect.php');
mysql_select_db("gracom1_widgets", $con) or die(mysql_error());
$result = mysql_query("SELECT * FROM customers INNER JOIN states on states.stateID = customers.stateID") or die(mysql_error());

echo "<table width='600px'>
  <tr>
    <th>Customer Name</th>
    <th>Phone</th>
    <th>Email</th>
    <th>Address</th>
<th>State</th> 
  </tr>";

while($row = mysql_fetch_assoc($result))
  {
  echo "<tr>";
  echo "<td>" . $row['custName'] . "</td>";
  echo "<td>" . $row['custPhone'] . "</td>";
  echo '<td><a href="mailto:' . $row['custEmail'] . '?subject=">' . $row['custEmail'] . "</a></td>";
  echo "<td>" . $row['custAddress'] . "</td>";
  echo "<td>" . $row['stateName'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);

?>

 

 

Wow thanks premisso you rule!

One last thing. How can I make it so it shows only the selected row (and not the whole table)? I know it has something to do with the $_GET Function in the URL as I've been reading. Just can't seem to figure it out.

Can't thank you enough.

 

 

 <?php
include('connect.php');
mysql_select_db("gracom1_widgets", $con) or die(mysql_error());
$result = mysql_query("SELECT `custName`, custPhone, custEmail, custID FROM customers") or die(mysql_error());

echo "<table width='600px'>
  <tr>
    <th>Customer Name</th>
    <th>Phone</th>
    <th>Email</th>
    <th>Details</th> 
  </tr>";

while($row = mysql_fetch_assoc($result))
  {
  echo "<tr>";
  echo "<td>" . $row['custName'] . "</td>";
  echo "<td>" . $row['custPhone'] . "</td>";
  echo '<td><a href="mailto:' . $row['custEmail'] . '?subject=">' . $row['custEmail'] . "</a></td>";
  echo '<td><a href="details.php?custID=' . $row['custID'] . '">Show</a></td>';
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);

?>

[premiso]

The code below assumes that it is detail.php:

 

 <?php
include('connect.php');
mysql_select_db("gracom1_widgets", $con) or die(mysql_error());

$custID = isset($_GET['custID']) ? (int) $_GET['custID'] : 0; // The ? and : are the ternary operator which is a short if/else
$result = mysql_query("SELECT * FROM customers INNER JOIN states on states.stateID = customers.stateID WHERE customers.custID = " . $custID) or die(mysql_error());

echo "<table width='600px'>
  <tr>
    <th>Customer Name</th>
    <th>Phone</th>
    <th>Email</th>
    <th>Address</th>
   <th>State</th> 
  </tr>";

while($row = mysql_fetch_assoc($result))
  {
  echo "<tr>";
  echo "<td>" . $row['custName'] . "</td>";
  echo "<td>" . $row['custPhone'] . "</td>";
  echo '<td><a href="mailto:' . $row['custEmail'] . '?subject=">' . $row['custEmail'] . "</a></td>";
  echo "<td>" . $row['custAddress'] . "</td>";
  echo "<td>" . $row['stateName'] . "</td>";
  echo "</tr>";
  }
echo "</table>";

mysql_close($con);

?>

 

See the comments, that is one way to do it, notice the (int) when calling the GET variable, this will cast it to an integer no matter what and will prevent SQL exploits from entering your code, since that should always be an int.

 

Un-tested of course, but "should" work, if you would like a further explanation of the ternary operator let me know.

[distant]

Thank you premisso!

One last thing.

Been surfing the web without luck for the last few hours.

What's the best method to alternate the row colors for better legibility?

Thanks once again.

[corrupshun]

try a light gray and then just gray

then you could use css so when you hover on any of them it's dark gray

Use white for the font color

[distant]

try a light gray and then just gray

then you could use css so when you hover on any of them it's dark gray

Use white for the font color

 

Thanks for replying corrupshun.

I can't use hover, as the color must be there the whole time (the color doesn't matter for now, I just want to learn how to do it).

I'm also pulling the data from a mysql database so I cannot address the rows directly.

I wish I could figure how to do this in php.

[premiso]

<?php
include('connect.php');
mysql_select_db("gracom1_widgets", $con) or die(mysql_error());
$result = mysql_query("SELECT `custName`, custPhone, custEmail, custID FROM customers") or die(mysql_error());

echo "<table width='600px'>
  <tr>
    <th>Customer Name</th>
    <th>Phone</th>
    <th>Email</th>
    <th>Details</th> 
  </tr>";

$i=0;
while($row = mysql_fetch_assoc($result))
  {
  $class = ($i % 2 == 0)?"alt":"noalt";
  echo "<tr class=$class>";
  echo "<td>" . $row['custName'] . "</td>";
  echo "<td>" . $row['custPhone'] . "</td>";
  echo '<td><a href="mailto:' . $row['custEmail'] . '?subject=">' . $row['custEmail'] . "</a></td>";
  echo '<td><a href="details.php?custID=' . $row['custID'] . '">Show</a></td>';
  echo "</tr>";
  $i++;
  }
echo "</table>";

mysql_close($con);

?>

 

The code to do it is called modulus (the % ) so doing the $i % 2 checks if there is a remainder, if there is then it will not equal 0, if there is not then it is equal to 0. I am not sure of the correct terms to put it so yea.

 

You just now need to make 2 styles, alt and noalt and do the table row backgrounds.