List by ID in Order!

PHP
1

Hello…
I-m trying to do the following !!
Listing data in the table !!
I would like…if the list has 4 rows…whetever the ID list is…make the ID list 1 2 3 4 !!
Example…if have a list with ID 1 3 3…make stay 1 2 3 !!

Follow the try next:

echo "<table class='tabela_dados' border='1'>
<tr>
	<td>ID</td>
	<td>Nome Empresa</td>
	<td>Responsável</td>
	<td>Telefone 1</td>
	<td>Telefone 2</td>
	<td>E-mail 1</td>
	<td>E-mail 2</td>
	<td>Endereço</td>
	<td>CEP</td>
	<td>Bairro</td>
	<td>AÇÃO 1</td>
	<td>AÇÃO 2</td>
	
</tr>
";

$sql = "SELECT ID FROM usuarios_dados WHERE Usuario='$usuario'";
$result = $conn->query($sql);
$num_rows = $result->num_rows;

$Novo_ID = 1;
for ($i = 0; $i < $num_rows; $i++) {
	$registro = $result -> fetch_row();
	$sql2 = "UPDATE usuarios_dados SET ID='$Novo_ID' WHERE ID='$Novo_ID'";
	$result2 = $conn->query($sql2);
	$Novo_ID++;
}

$sql = "SELECT * FROM usuarios_dados";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // output data of each row
  while($row = $result->fetch_assoc()) {
    echo "<tr>
	<td>$row[ID]</td>
	<td>$row[Nome_Empresa]</td>
	<td>$row[Responsavel]</td>
	<td>$row[Telefone_1]</td>
	<td>$row[Telefone_2]</td>
	<td>$row[Email_1]</td>
	<td>$row[Email_2]</td>
	<td>$row[Endereço]</td>
	<td>$row[CEP]</td>
	<td>$row[Bairro]</td>
	<td>
	<form method='post' action='Editar_Dados.php'>
	<input type='hidden' name='usuario' value='$usuario'>
	<input type='hidden' name='senha' value='$senha'>
	<input type='hidden' name='ID' value='$row[ID]'>
	<input type='submit' style='padding: 10px;' value='EDITAR'>
	</form>
	</td>
	<td>
	<form method='post' action='Deletar_Dados.php'>
	<input type='hidden' name='usuario' value='$usuario'>
	<input type='hidden' name='senha' value='$senha'>
	<input type='hidden' name='ID' value='$row[ID]'>
	<input type='submit' style='padding: 10px;' value='DELETAR'>
	</form>
	</td>
	</tr>
	";
  }
} else {
  echo "0 results";
}

$conn->close();
2

Not sure !!
why everything !!
is a double exclamation !!

Have you tried putting an ORDER BY on your select query?

4 Likes
3

If all you want to do is display a line number, rather than the ID itself, then just create a variable and display it, then increment it inside your loop.

You need to read up on Prepared Statements and stop concatenating variables directly into your queries.

What is the point of this query?

$sql2 = "UPDATE usuarios_dados SET ID='$Novo_ID' WHERE ID='$Novo_ID'";

It doesn’t change the value of the ID. Maybe you intended to set it to the value of $i, or maybe you intended to be using $registro['ID'] in your WHERE clause?

There’s not very much point to the $Novo_ID variable - you already have $i which, in your loop, is always one less than $Novo_ID, so you might as well just use $i +1.

Does ID need to be unique across the entire table, or just for each user? If it’s per user, then once you use the correct value in your UPDATE query it should work. If not, I would expect some duplicates.

Can you confirm, please, whether you want to actually change the ID values, or just display incrementing numbers?

4

If you control you list from a query you wouldn’t have to select everything.

Here’s an example → 

// Use a prepared statement to avoid SQL Injection
$stmt = $pdo->prepare('SELECT title FROM puzzle_images WHERE category = :category ORDER BY date_added DESC');
$stmt->bindParam(':category', $category, PDO::PARAM_STR);

$stmt->execute();
$titles = $stmt->fetchAll(PDO::FETCH_COLUMN);
1 Like
5

Answer “droopsnoot”:

I confirm that I want to change ID values. How Could It be the correct code ??

6

To “All”:
I resolved the problem: The correct is:

$sql = "SELECT * FROM usuarios_dados";
$result = $conn->query($sql);
$num_rows = $result->num_rows;

for ($i = 1; $i <= $num_rows; $i++) {
	$registro = $result -> fetch_assoc();
	$sql2 = "UPDATE usuarios_dados SET ID='$i' WHERE ID='$registro[ID]'";
	$result2 = $conn->query($sql2);
}
7

While I can see that this will give you a table which contains consecutive ID numbers with no gaps, they aren’t in any particular order because you don’t retrieve the rows in any particular order. Presumably that doesn’t matter, the object was to remove gaps and duplicate IDs. For the latter, once you have removed duplicates, consider modifying the table to not allow duplicate IDs. “ID” would usually indicate a means to identify an individual row, so should really be marked as “unique”.

8

I got the answer:

$sql = "SELECT * FROM usuarios_dados WHERE Usuario='$usuario'";
$result = $conn->query($sql);
$num_rows = $result->num_rows;

for ($i = 1; $i <= $num_rows; $i++) {
	$registro = $result -> fetch_assoc();
	$sql2 = "UPDATE usuarios_dados SET ID='$i' WHERE ID='$registro[ID]' AND Usuario='$usuario'";
	$result2 = $conn->query($sql2);
}

$sql = "SELECT * FROM usuarios_dados WHERE Usuario='$usuario'";
$result = $conn->query($sql);