Creating a table on HTML with information from various MySQL tables

Question

i have a website i am constructing for a school project, in which i already have various webpages where i get tabular data from my database through the use of While and Foreach.
But on this page in question, i am attempting to retrieve data from various tables, in order for a user to choose a category, and a text and submit.
But for some reason it isn't outputting any data. If i attempt with only one table and without using the table.field method, and only typing the fields, it works.
But from what i know, to retrieve from various tables i have to do so.
Can annione help me out on this?

<html>
<head>
<script type="text/javascript" >
</script>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>
<?php
mysql_connect("localhost","root","") or die("problema na conexao");
mysql_select_db("trabalho1");
$query = "SELECT texto.titulo,texto.ID,categoria.categoria,categoria.id FROM categoria,texto";
$results = mysql_query($query) or die(mysql_error());   
echo"<center>";
echo "<table border='2'>\n";
echo "<form id='formulario' name='post' method='post' onsubmit='return validar(this) action='../inserir/inseretexto.php>'";
echo "<button type='submit'>Submeter</button>";
echo "<tr><td colspan ='2'>Historico de Newsletters</td><td colspan='2'>Enviar Newsletter</td></tr>";
echo "<tr><td colspan='2'>Texto </td><td colspan='2'>Categoria</td></tr>";
while ($row = mysql_fetch_assoc($results)) {    
    foreach ($row as $campo=>$valor) {
        if ($campo == "texto.titulo") {
            echo "<tr><td>'".$valor."'</td>";
        }

        if ($campo == "texto.ID") {
            echo "<td><input type='radio' name='nome' value='".$valor."'></td></tr>";
        }

        if ($campo == "categoria.categoria") {
            echo "<td>'".$valor."'</td>";
        }

        if ($campo=="categoria.id") {
            echo "<td><input type='radio' name='nome' value='".$valor."'></td></tr>";
        }
    }
}
echo "</form>";                                                             
echo "</table>";
echo "</center>";
?>
</body>
</html>

Added: Since both tables have a field called id, it won't let me simply put the field names, i have to also put the table name like i did. And yes, i have verified and both tables are populated with data, they work fine on other pages.

None. This was in order to select data from one and another, and add them to a table which i was going to use to join them. — Heatmanofurioso, Apr 21 '14 at 15:39
Using two tables in the same query where no relationship exists, creates a Cartesian product (having P x Q records; where P: number of records in table A, Q: number of records in table B) which creates huge load on your database server. So I would suggest to execute two queries and iterate the two results. Also echoing static strings using PHP creates an unnecessary procession on your web server too. Further your code could be optimized for performance. — Amil Waduwawara, Apr 21 '14 at 15:43
But if both tables have the same number of records, wouldn't it be okay? Also, i estimate that i won't be having much data on both of these tables. 30 records maximum probably. Isn't there a way to solve it this way? If not then i will follow up your suggestion — Heatmanofurioso, Apr 21 '14 at 15:47
Then you can execute two queries and generate a single PHP array and then iterate that array to generate HTML table. — Amil Waduwawara, Apr 21 '14 at 15:48
See my answer. Please accept the answer if it's right for you. — Amil Waduwawara, Apr 21 '14 at 16:07

score 0 · Accepted Answer · edited May 23 '17 at 12:05

This is what I suggest (I didn't consider much code optimization).

<?php
$data = array();

mysql_connect('localhost', 'root', '') or die('problema na conexao');
mysql_select_db('trabalho1');

// Use the same field-names/aliases: id, info
$query = 'SELECT ID AS id, titulo AS info FROM texto';
$results = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_assoc($results)) {
    $data[] = $row;
}

// Use the same field-names/aliases: id, info
$query = 'SELECT id, categoria AS info FROM categoria';
$results = mysql_query($query) or die(mysql_error());

while ($row = mysql_fetch_assoc($results)) {
    $data[] = $row;
}
?>
<html>
<head>
<script type="text/javascript">
</script>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
</head>
<body>

<center>
<table border='2'>
<form id='formulario' name='post' method='post' onsubmit='return validar(this) action='../inserir/inseretexto.php>
<tr><td colspan ='2'><button type='submit'>Submeter</button></td></tr>
<tr><td colspan ='2'>Historico de Newsletters</td><td colspan='2'>Enviar Newsletter</td></tr>
<tr><td colspan='2'>Texto </td><td colspan='2'>Categoria</td></tr>
<?php
foreach ($data as $row) {
    echo '<td><input type="radio" name="nome" value="' . $row['id'] . '></td></tr>' . 
        '<tr><td>' . $row['id'] . '</td>';
}
?>
</form>
</table>
</center>
</body>
</html>

PS: Read about my response about double quotations. Should I use curly brackets or concatenate variables within strings?

It still returned empty. But i understand the logic. Any idea on the why? I'l keep trying to solve it myself, but any suggestion? — Heatmanofurioso, Apr 21 '14 at 16:09
First try executing the queries directly on database. You may use print_r(VARIABLE) to debug the results inside PHP. — Amil Waduwawara, Apr 21 '14 at 16:14

Creating a table on HTML with information from various MySQL tables

1 Answers1