4 Replies - 3829 Views - Last Post: 11 June 2012 - 11:59 PM Rate Topic: -----

#1 GolfyMcG  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 14
  • Joined: 22-November 11

Create Table and Immediately Copy Data Over

Posted 11 June 2012 - 02:06 AM

I have a table of users called 'cumulative' and those users can apply for a higher level of permission. This application sends an email to the administrator (me) and I review it. The data is also saved in a database called applications. In the email I supply myself with two links that accepts/rejects the user. In the case of accepting, I am having an issue with one small piece of it but I cannot understand why it won't work:

Below is a few lines of php/mysql code that show where the problem seems to appear:

$create = mysql_query("CREATE TABLE $schoolname LIKE johns_hopkins_university");
$activated1 = mysql_query("UPDATE cumulative SET schoolname='$schoolname' WHERE id='$id'");
$activated2 = mysql_query("UPDATE applications SET role='Accept' WHERE id='$id'");
$captain = mysql_query("
        INSERT INTO $schoolname
        SELECT *, 'Captain', 'Admin'
        FROM cumulative 
        WHERE email='$email'") or die("Copy Query didn't work");


First, the creating of the new table ($schoolname) works. Next, denoting this activation on both the cumulative user table, and the applicant table, works great, but what doesn't work is that when I try to copy over information from cumulative to the new table, it won't do it. I have included the die function but that isn't triggered and the copy supposedly is being processed but there is no resulting row in the new table.

Is there perhaps a different way to create and add data to a new table other than what I have above? Is there a different underlying issue??

Thank you in advance for the help

Is This A Good Question/Topic? 0
  • +

Replies To: Create Table and Immediately Copy Data Over

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3572
  • View blog
  • Posts: 10,413
  • Joined: 08-June 10

Re: Create Table and Immediately Copy Data Over

Posted 11 June 2012 - 02:12 AM

why in the world do you create new tables for every school? this certainly violates Database Normalisation. (which, if applied, wouldn’t show the problems you have)
Was This Post Helpful? 4
  • +
  • -

#3 GolfyMcG  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 14
  • Joined: 22-November 11

Re: Create Table and Immediately Copy Data Over

Posted 11 June 2012 - 02:17 AM

I read the first paragraph of that and realized I'm too tired to think. I'm going to read it all tomorrow though because I am teaching this all to myself. I just received a degree in Mechanical Engineering and have no training in CS so thank you for the lesson. I had wondered what was the normal practice for creating/updating tables and databases. I appreciate the contribution Dormilich.
Was This Post Helpful? 0
  • +
  • -

#4 GolfyMcG  Icon User is offline

  • New D.I.C Head

Reputation: 3
  • View blog
  • Posts: 14
  • Joined: 22-November 11

Re: Create Table and Immediately Copy Data Over

Posted 11 June 2012 - 11:42 PM

Dormilich I'm sure this sounds over-emphatic but thank you so much for sharing that link. I've spent the last view hours drawing out the relationships that all of my tables should have I can only imagine the difference it will make in the long run.

Thank you again.
Was This Post Helpful? 2
  • +
  • -

#5 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3572
  • View blog
  • Posts: 10,413
  • Joined: 08-June 10

Re: Create Table and Immediately Copy Data Over

Posted 11 June 2012 - 11:59 PM

thank Atli and Frinny for that, they have shown me that in the first place.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1