0

So I have two tables, one called 'questions' and one called 'tests'. Each row in 'questions' has the columns 'id','test' and several other columns not relevant to this question. The 'tests' table has the columns 'id','name' and several others not relevant.

Basically, on my website, every time someone creates a test, a new row is created in 'tests' and its given an 'id'. From there, every question the user makes, creates a new row in the 'questions' table, each question is given an 'id' as well as 'test' column having the same number as the 'id' from the 'tests' table to which it belongs.

Yesterday we mistakenly deleted several rows from the 'tests' table, however all the questions are still in the 'questions' table, meaning we just need to find which rows in the 'questions' table have a 'test' value that isn't linked to any 'id' in the 'tests' table, so this is the .php script we came up with:

<?php
$conn = mysql_connect ('localhost', 'hidden',  'hidden') or die ('Error connecting');
if (!$conn) {
    die('Could not connect: ' . mysql_error());
}
mysql_select_db('hidden', $conn);
$sql = "SELECT * FROM questions";
$result = mysql_query($sql,$conn);

while ($row = mysql_fetch_array($result)) {
    $test = $row['test'];
    $questionID = $row['id'];
    $sql2 = "SELECT * FROM tests where id = $test";
    $result2 = mysql_query($sql2, $conn);
    $row2 = mysql_fetch_array($result2);
    $num_rows = mysql_num_rows($result2);
    if ($num_rows == 0) {
        echo "Unlinked question: $questionID (test $test) <br/>";
    }
}
?>

However, it doesn't echo any "Unlinked question.. " which would help us identify which tests we have lost and need to recreate, giving them the correct 'id' to link to the 'questions' table 'test' column. Is there another way to fix this problem? And yes, we have do backup the tables, but unfortunately we only do it every other week, and most of the deleted tests were recent.

Update: 

$sql = "
SELECT q.id AS QuestionID
FROM questions q
LEFT JOIN tests t
ON  (q.test = t.id)
WHERE q.test IS NOT NULL AND t.id IS NULL";

$result = mysql_query($sql,$conn) or die(mysql_error());
while ($row = mysql_fetch_array($result)) {
$questionID = $row['QuestionID'];
$testID = $row['test'];
echo $row;
echo "The question with the id: $questionID is not linked to the test: $testID";
}

?>
Omair Vaiyani
  • 552
  • 7
  • 28

2 Answers2

1

Why wouldn't you do this in a single SQL statement??

SELECT
    q.id AS QuestionID,
    CASE WHEN q.test IS NOT NULL AND t.id IS NULL THEN 1 ELSE 0 END AS UnlinkedQuestion
FROM
    questions q
LEFT JOIN
    tests t
    ON  (q.test = t.id)

OR, if you only want to return unlinked question id, simply move the logic into the WHERE clause:

SELECT
    q.id AS QuestionID
FROM
    questions q
LEFT JOIN
    tests t
    ON  (q.test = t.id)
WHERE
    q.test IS NOT NULL AND
    t.id IS NULL

NOTE:

I would highly recommend that you add foreign key constraints to your question table on the test id, so that you can prevent the same error with accidentally deleting records from your test table.

  • I'm pretty new to php, well programming in general (started about a month ago), would love an explanation to your answer, or have you used the correct variables and I can just copy the exact code? – Omair Vaiyani Dec 29 '12 at 15:40
  • You may be able to copy it - I don't know your table columns because you are using '*' in your select statement, so you may need to tweak this a little. Basically, you are just joining the two tables on that test id. If the question table has a test id, but doesn't match with the test table, that indicates your unlinked question –  Dec 29 '12 at 15:41
  • I've updated the question with the code you gave, but I'm quite sure what to define the variable as that I need to echo? Also, we used to have foreign key restrains but a lot of the tests uploaded by users had questions missing – Omair Vaiyani Dec 29 '12 at 16:03
  • does `FROM questions q` make `q` a reference to `questions`? – jeremy Dec 29 '12 at 16:04
  • @Nile yea, 'q' is simply the alias –  Dec 29 '12 at 16:05
  • @OmairVaiyani - First, does the code in your update work for you? Is it echo'ing the question ids? –  Dec 29 '12 at 16:07
  • Can I ask what the 'AS' part does? Does it create a new variable called 'QuestionID' in this case? And no it's not echoing anything – Omair Vaiyani Dec 29 '12 at 16:09
  • @OmairVaiyani - 'AS' simply indicates that you are providing an alias for the column (instead of 'id' it will return as 'QuestionID'. I don't know what $unlinkedQuestion has in it, but that is probably your issue. Why can't you do this in your while loop: `echo $row;` –  Dec 29 '12 at 16:13
  • That makes more sense, I've added the echo $row; as well as an echo that should make it more clear what I expect from the script, added the whole code to the Update part of my question, however it's not echoing anything (i've checked for errors - nothing, the script is running) @nile – Omair Vaiyani Dec 29 '12 at 16:19
  • @OmairVaiyani - Does the query return anything? Are there even any records that match that logic in the query? If you can, you should try to execute the query inside of a MySQL client like [http://www.devart.com/dbforge/mysql/studio/](http://www.devart.com/dbforge/mysql/studio/) –  Dec 29 '12 at 16:21
  • It doesn't return anything. Well I assume the logic is searching for questions that have a value in q.test which doesn't match any value in t.id? If so, then yes, there should be hundreds of records - I've manually found many questions myself, but I need this to work in order to not spend the next few days doing this, I'll have a look at some cheaper mysql clients, however I feel they may take some time to learn – Omair Vaiyani Dec 29 '12 at 16:28
  • @OmairVaiyani - Devart has a free/express version, which is all you need. Based on your description of the tables, the query should give you exactly what you are looking for. You should take an example of a question you **know** has a test value, but no corresponding test and filter the query to show you what it returns -- *troubleshoot* –  Dec 29 '12 at 16:30
  • The new code you've posted in your answer works perfectly for me. – jeremy Dec 29 '12 at 16:31
  • @Nile - it should, it's very simple. :) –  Dec 29 '12 at 16:33
  • It's working now! Not sure why I had to do the troubleshoot thing first then return it back to the code above but it works. Thanks for your help guys :) – Omair Vaiyani Dec 29 '12 at 16:40
  • @OmairVaiyani - Glad to hear! And, again, I would recommend looking into foreign constraints as to help prevent this same issue in the future. –  Dec 29 '12 at 16:41
0

Remember to add or die(mysql_error()) to the end of your mysql_* functions so it's easier to identify any issues.

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

jeremy
  • 9,965
  • 4
  • 39
  • 59
  • I see, I'll check out the PDO tutorial once I sort this issue out, thanks for the heads up! – Omair Vaiyani Dec 29 '12 at 15:44
  • It didn't, I echoed the $num_rows before the if clause, and checked the page, there were only '1' on the page, meaning it's only querying the linked questions, if you see what I mean? – Omair Vaiyani Dec 29 '12 at 15:52
  • I dont understand... can you put the `or die(mysql_error())` on all of your mysql functions so I can further assist you? – jeremy Dec 29 '12 at 15:55
  • That would look something like: `$result2 = mysql_query($sql2, $conn) or die(mysql_error());` on all of your `mysql_*` functions – jeremy Dec 29 '12 at 15:58
  • @Nile - I think he is looking for the questions in the question table where there is a value in the column 'test', which should match with the 'id' column in test, but does not because he had accidentally deleted some records in the test table. –  Dec 29 '12 at 15:58
  • @Nick - I understand what he's trying to do, I don't understand what he was trying to say in that comment ^. – jeremy Dec 29 '12 at 15:59
  • @Nile - I understand; yes, I don't think his response relates to your answer. –  Dec 29 '12 at 16:01
  • Thats correct, basically I need the script to echo the 'id' of each question followed by the value of the 'test' column so I know what to put in the 'id' column on the 'tests' table when I recreate the rows. @Nile,I added the or die(mysql_error()); but the script is functioning, so there is nothing in the error_log – Omair Vaiyani Dec 29 '12 at 16:06
  • @OmairVaiyani - have you added the `or die(mysql_error())` to your original code also? – jeremy Dec 29 '12 at 16:06
  • @OmairVaiyani - If you don't add that to the script as Nile suggests, you are missing out on some potentially very helpful information about your problem. –  Dec 29 '12 at 16:08
  • Yes I've added the or die to my original, but there is nothing in the error_log, unless I'm supposed to find an error somewhere else? – Omair Vaiyani Dec 29 '12 at 16:16
  • I can't give reputations, says I need to have a reputation of 15 myself, but I ticked it if that helps :) – Omair Vaiyani Dec 29 '12 at 16:46