List by ID in Order !!

[Legendary_003]

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();

 

[ginerjm]

you are not enabling php error checking or you  would  be seeing a  lot of  error messages.  Turn it on!  For example:

$row[ID]

is an invalid line.  Any reference to an array value  must  either be  a  php variable  or a  text  value wrapped in quotes to identify it as text or else a number.

After that - I don't have a clue what you are trying to write.

$row['ID']

This is how it needs to be in your case.

Edited March 21 by ginerjm

[Barand]

Record IDs ...

• should be unique.
• should never be changed
• should never be reallocated

Breaking those rules destroys the integrity of your database.

And as for you update query, what is the point of setting the id to X where the id = X. That is like you changing your username on this forum to "Legendary_003"

[ginerjm]

your initial post is meaningless.

I-m trying to do the following !! (I THINK YOU MEANT TO write I'm instead of I-m.  At least I hope you do.)
	What is the following?  You don't say what it is you are trying to do.

Listing data in the table !!
	Listing data is a normal procedure for programmers to accomplish.  What is the problem?  You don't say.

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 !!
	If you have an "ID" of some sort in your database then it should not be altered and it should be UNIQUE.  Yet - you are saying that when displayed you want the "ID" to be modified to show your records with a new consecutive list of ids that are not what your database should be showing.  WHY WOULD ANYONE DO THAT?  Maybe you need to explain in English what it is you are trying to do.

 

[Barand]

You need to output a row number, not the actual id. One way is to increment a $rowno variable each time you output a row of your query results and output that.

Alternatively, there are a couple of ways you can do it with a single query.

Test data

TABLE: reindeer
+----+---------+
| id | name    |
+----+---------+
|  1 | Comet   |
|  3 | Cupid   |
|  4 | Dasher  |
|  6 | Vixen   |
|  8 | Prancer |
| 10 | Dancer  |
| 15 | Donner  |
| 16 | Blitzen |
+----+---------+

1. Using an SQL variable

   SELECT @rowno := @rowno+1 as rowno
        , name
        , id
   FROM reindeer
   JOIN (SELECT @rowno := 0) as init
   ORDER BY name;

2. Using the ROW_NUMBER() window function (MariaDB v11 or MySql v8)

   SELECT ROW_NUMBER() OVER (order by name) as rowno
        , name
        , id
   FROM reindeer
   ORDER BY name;

Both the above output

+-------+---------+------+
| rowno | name    | id   |
+-------+---------+------+
|     1 | Blitzen |   16 |
|     2 | Comet   |    1 |
|     3 | Cupid   |    3 |
|     4 | Dancer  |   10 |
|     5 | Dasher  |    4 |
|     6 | Donner  |   15 |
|     7 | Prancer |    8 |
|     8 | Vixen   |    6 |
+-------+---------+------+

 

[Danishhafeez]

your current approach is flawed because you're updating the IDs in a loop without considering the existing IDs in the database. Also, the update query inside the loop doesn't seem to be properly updating the IDs.

<?php

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 * FROM usuarios_dados WHERE Usuario='$usuario'";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    $Novo_ID = 1;
    while ($row = $result->fetch_assoc()) {
        // Update the ID in the database
        $old_ID = $row['ID'];
        $sql_update = "UPDATE usuarios_dados SET ID='$Novo_ID' WHERE ID='$old_ID'";
        $conn->query($sql_update);
        
        // Output the row with the updated ID
        echo "<tr>
            <td>$Novo_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='$Novo_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='$Novo_ID'>
            <input type='submit' style='padding: 10px;' value='DELETAR'>
            </form>
            </td>
            </tr>";
        
        $Novo_ID++;
    }
} else {
    echo "0 results";
}

echo "</table>";

$conn->close();
?>

This code will assign sequential IDs starting from 1 to each row in the database table while displaying the data in the HTML table. Make sure to replace $usuario and $senha with appropriate variables if they are defined elsewhere in your code.

Best Regard

Danish Hafeez | QA Assistant

ICTInnovations

[Barand]

@Danishhafeez,

I am trying to decide if your post is a product of Artificial Intelligence or Natural Ignorance. That a member of ICTInnovation's QA team would advocate code which changes record IDs should be a huge red flag to any of their prospective clients.

Just my 0.02 worth.

[Legendary_003]

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);