Tag: index
Nabin Nepal (Starx) index php
October 7, 2012

$row['id'] – undefined index

Question by kdot

kdotkdot

I am new to PHP. I am practicing how to delete a row in mysql using PHP.

I have this code:

<?php

$con = mysql_connect("localhost","root","");

mysql_select_db("usersdb", $con);


echo "<table border='1'>
<tr>
<th>USERNAME</th>
<th>PASSWORD</th>
<th>ACTION</th>
</tr>";

$result = mysql_query("SELECT * FROM users");
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['username'] . "</td>";
echo "<td>" . $row['password'] . "</td>";
echo "<td>";
echo '<a href="index.php?delete='.$row['id'].'">Delete</a></html>';
echo "</td>";
echo "</tr>";
}
echo "</table>";

if(isset($_GET['delete']) and is_numeric($_GET['delete']))
{
    mysql_query("DELETE FROM Persons WHERE `id` = '".$_GET['delete']."'");
}

?>

When I tested it on the browser, it gives this error: 

Notice: Undefined index: id in C:wampwwwindex.php on line 22

The error is pointing at the $row[‘id’]. I tried to defined it as $id = $row[‘id’] and replace $id on the line where $row[‘id’] is located. But in the end, I still get the same error. How can I solve this kind of error?

Answer by Starx

StarxStarx

It means your data row, does not have a column named id or it can’t find it for some reason.

Verify this, by doing a print_r($row) inside the loop like this.

$result = mysql_query("SELECT * FROM users");
while($row = mysql_fetch_array($result))
{
    print_r($row);
}
Nabin Nepal (Starx) index mysql query
April 3, 2012

How to Speed up Comparison of two tables in MySQL

Question by Deepak

DeepakDeepak

I have two large tables in MySQL, one containing about 6,00,000 records and another containing about 90,000. I have one common field in these two tables on the basis of which I want to compare the records. I indexed both the tables on this common field but still query execution takes a very long time. Following is the query I used:

SELECT a.url,a.title,a.description,a.jobreferenceno,a.location,a.state,
    a.country,a.created_datetime,a.postalcode,a.company
FROM TABLE1 as a
WHERE EXISTS (
    select b.checkfield
    from TABLE2 as b where a.checkfield=b.checkfield
);

checkfield is the common column in both the tables.

Any suggestions on how to speed it up?

Answer by Roland Bouman

Roland BoumanRoland Bouman

subqueries, like the EXISTS subquery you used here, are notoriously slow in MySQL. You should convert them to a JOIN if you have the chance. A JOIN query for your example would look like this:

SELECT a.url
,a.title
,a.description
,a.jobreferenceno
,a.location
,a.state
,a.country
,a.created_datetime
,a.postalcode
,a.company 
FROM TABLE1 as a 
INNER JOIN TABLE2 as b 
ON         a.checkfield = b.checkfield

(Note that this is not entirely the same as your original: here, any row from TABLE1 for that matches multiple rows in TABLE2 will be returned multiple times. If checkfield is unique in both tables, the result will be the same.)

That said, it is unclear how this really helps – you’re not really comparing rows here, simply selecting those rows from TABLE for which there is at least one row in TABLE2 that happens to have the same value in checkfield

(at any rate, checkfield should be indexed in both tables to help the efficiency of these queries )

Answer by Starx

StarxStarx

Since there is one field in common, you can use INNER JOIN

SELECT a.url, a.title, a.description, a.jobreferenceno, a.location, a.state, a.country, a.created_datetime, a.postalcode, a.company FROM table1 a 
INNER JOIN table2 b USING (checkfield)
...

Please fill the form - I will response as fast as I can!