Pulling Unrelated Data From Two Tables In Same DB

[sintax63]

OK, I've scoured the forms on here, searched and tutorial read and even pulled out an old PHP book. Alas, I'm stumped.

 

First off, my two tables that I'm trying to pull data from:

 

rubric

-------------------------------------

---- code ---- | ---- title --------

-------------------------------------

ABCD            | Category Name

 

 

schedule

----------------------------------------------------

------ name ------ | ------ short_title ------

----------------------------------------------------

ABCD-123-4569  | Title Is Here

 

 

Now, none of these share the same value, per say. rubric.code shares the first THREE letters from schedule.name, but that is all.

What I'm trying to do is something similar to the old:

 

A

apple

automobile

 

B

banana

basket

bench

 

C

car

child

crumb

 

... etc, etc, where the letter heading is actually the rubric.title value and then underneath it it lists all the matching short_title from

schedule where the first four letters of schedule.name match the value of rubric.code - if that even makes sense.

 

Unfortunately these are the tables I have to work with. If anyone out there has any ideas, I would be thrilled to hear them. Another

thing that is driving me crazy is that this server I'm working on doesn't have error reporting turned on so I've been stuck with just a

white screen to let me know something is amiss.

 

I'm running PHP Version 5.3.3-7+squeeze8 if that helps.

 

Thank you!

 

[Jessica]

SELECT rubric.title, rubric.code, schedule.name, schedule.short_title

FROM schedule

INNER JOIN rubric

ON rubric.code = substr(schedule.name, 4)

ORDER BY rubric.code

 

Should give you every schedule with the matched title & code. Then when you loop through them you store which code you're on, and if the current one is different than the previous, before printing the schedule info, print out the new rubric info.

 

Also, to clarify - they ARE related. Just in a bad way. You should add a primary key field.

[sintax63]

Thanks, jesirose - I'll give that a try right now!

[marcus]

Similar approach:

 

SELECT * FROM `rubric` AS r, `schedule` AS s WHERE s.name LIKE CONCAT(r.code,'%')

[sintax63]

Hmmm. Still just getting an empty area where the results should be displayed. My current code:

 

<?

$query	= "SELECT rubric.title, rubric.code, schedule.name, schedule.short_title FROM schedule INNER JOIN rubric ON rubric.code = substr(schedule.name, 3) ORDER BY rubric.code";
$result	= mysql_query($query);

$i = 0;
    
while( $row = mysql_fetch_assoc( $result ) ) {

$code			= $row['code'];
$title				= $row['title'];
$name			 = $row['name'];
$short_title		= $row['short_title'];

include("../includes/globals.php");

$i++;

echo "<div id=\"listZoom2\"><a href=\"course.php?name=$shortName\">$shortTitle</a></div>";
       
}

?>

 

Also, where would I put the PHP to spit out the rubric.title?

[sintax63]

Similar approach:

 

SELECT * FROM `rubric` AS r, `schedule` AS s WHERE s.name LIKE CONCAT(r.code,'%')

 

Wow, that code worked great! I now have a huge list of all the results.

I'm just now sure now how to get the rubric.title to show and group them accordingly.

 

Thanks!

[marcus]

Woops, you wanted first three

 

SELECT * FROM `rubric` AS r, `schedule` AS s WHERE s.name LIKE CONCAT(SUBSTR(r.code,0,3),'%')

[sintax63]

What I'm getting now is a massive list of just the first rubric.title over and over again. 

 

<?

$query	= "SELECT * FROM `rubric` AS r, `schedule` AS s WHERE s.name LIKE CONCAT(SUBSTR(r.code,0,3),'%') GROUP BY s.short_title ASC";
$result	= mysql_query($query);

    
while( $row = mysql_fetch_assoc( $result ) ) {

	$code			= $row['code'];
	$title				= $row['title'];
	$name			 = $row['name'];
	$short_title		= $row['short_title'];

include("../includes/globals.php");


	if ($title != $prev_row) { 

		echo "<a name=\"$title\"></a> <h3>$title</h3> \n\n";  

	} else {

		echo "<div id=\"listZoom2\"><a href=\"course.php?name=$shortName\">$shortTitle</a></div>";

		$prev_row = $title; 
       
	}

}

?>

[marcus]

In your if statement you need to assign $prev_row to $title or else your code will never reach that else statement.

[xyph]

It's time to normalize your database my friend!

 

Add an id field to both tables, and a foreign key placeholder in your schedule table, as I've done below.

 

--
-- Table structure for table `rubric`
--

CREATE TABLE IF NOT EXISTS `rubric` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(10) NOT NULL,
  `title` varchar(30) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `rubric`
--

INSERT INTO `rubric` (`id`, `code`, `title`) VALUES
(1, 'ABCD', 'Category 1'),
(2, 'CDEF', 'Category 2');

-- --------------------------------------------------------

--
-- Table structure for table `schedule`
--

CREATE TABLE IF NOT EXISTS `schedule` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rubric_id` int(10) unsigned NOT NULL,
  `name` varchar(15) NOT NULL,
  `short_title` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `rubric_id` (`rubric_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `schedule`
--

INSERT INTO `schedule` (`id`, `rubric_id`, `name`, `short_title`) VALUES
(1, 1, '123-4569', 'Title Is Here'),
(2, 2, '456-7812', 'Another One'),
(3, 2, '789-7890', 'Foo'),
(4, 1, '852-1234', 'Bar');

 

You can then use a solution very similar to jesirose's.

 

<?php

$sql = new MySQLi('localhost', 'root', '', 'db');

$q = 'SELECT rubric.id as rubric_id, rubric.title, rubric.code, schedule.id, schedule.name, schedule.short_title
FROM schedule
INNER JOIN rubric
ON rubric.id = schedule.rubric_id
ORDER BY rubric.title';

$r = $sql->query($q);

// Check if query returned FALSE
if( $r === FALSE ) {
echo 'Query Failed';
} else {
// This will hold the current rubric title we're looping through
$current_rubric = FALSE;
while( $row = $r->fetch_assoc() ) {
	// This checks if a current rubic hasn't been defined, or if a new rubric has started
	if( $current_rubric === FALSE || $current_rubric != $row['rubric_id'] ) {
		// Update the current rubric to the one in this loop
		$current_rubric = $row['rubric_id'];
		// Output the rubric name, only when we've moved on to a new one
		echo "<h3>{$row['title']}</h3>";
	}
	echo "<b>{$row['short_title']}</b> <small>({$row['code']}-{$row['name']})</small><br>";
}
}

?>

 

Outputs

 

<h3>Category 1</h3>
<b>Title Is Here</b> <small>(ABCD-123-4569)</small><br>
<b>Bar</b> <small>(ABCD-852-1234)</small><br>
<h3>Category 2</h3>
<b>Another One</b> <small>(CDEF-456-7812)</small><br>
<b>Foo</b> <small>(CDEF-789-7890)</small><br>

[sintax63]

Getting closer I think. Right now I get all the headings displayed correctly but under each of them I have the same, single schedule.short_title repeated over and over.

 

$query	= "SELECT * FROM `rubric` AS r, `schedule` AS s WHERE s.name LIKE CONCAT(SUBSTR(r.code,0,3),'%')";
$result	= mysql_query($query);
    
while( $row = mysql_fetch_assoc( $result ) ) {

	$code			= $row['code'];
	$title				= $row['title'];
	$name			 = $row['name'];
	$short_title		= $row['short_title'];

include("../includes/globals.php");

	if ($title != $prev_row) { 

		echo "<a name=\"$title\"></a>$title< \n"; 
	} 

	echo "<ul> \n";
	echo "<li><div id=\"listZoom2\"><a href=\"course.php?name=$shortName\">$shortTitle</a></div></li> \n";
	echo "</ul> \n\n";	

	$prev_row = $title; 

}

[sintax63]

xyph -

 

Thanks for all that. I have done as you suggested and am getting this error

 

Notice: Undefined variable: sql in /home/public_html/index.php on line 206 Fatal error: Call to a member function query() on a non-object in /home/public_html/index.php on line 206

 

using this code:

 

<?php

ini_set('display_errors',1);
error_reporting(-1);

$q = 'SELECT rubric.id as rubric_id, rubric.title, rubric.code, schedule.id, schedule.name, schedule.short_title
FROM schedule
INNER JOIN rubric
ON rubric.id = schedule.rubric_id
ORDER BY rubric.title';

$r = $sql->query($q);

// Check if query returned FALSE
if( $r === FALSE ) {
echo 'Query Failed';
} else {
// This will hold the current rubric title we're looping through
$current_rubric = FALSE;
while( $row = $r->fetch_assoc() ) {
	// This checks if a current rubic hasn't been defined, or if a new rubric has started
	if( $current_rubric === FALSE || $current_rubric != $row['rubric_id'] ) {
		// Update the current rubric to the one in this loop
		$current_rubric = $row['rubric_id'];
		// Output the rubric name, only when we've moved on to a new one
		echo "<h3>{$row['title']}</h3>";
	}
	echo "<b>{$row['short_title']}</b> <small>({$row['code']}-{$row['name']})</small><br>";
}
}

?>

 

My line 206 is:

 

$r = $sql->query($q);

[xyph]

You need to define $sql

 

See the first line of code in my example

$sql = new MySQLi('localhost', 'root', '', 'db');

http://php.net/manual/en/mysqli.construct.php

[sintax63]

Sorry about that. I had it declared elsewhere but I went and plugged it back into your original code. I'm now getting just an empty results area (no error message and the page fully loads)

 

The rubric_id field in my schedule table contains 0 for each entry. Is that how it should be or should they be blank / unique?

 

[xyph]

It should be the ID of the rubric you want the schedule to appear under.

 

It's know as a 'parent' or 'foreign key'

 

Most SQL flavours even have specific foreign key functionality; for example, you can set it so when a parent is DELETEd, all of it's children are deleted as well

http://www.sitepoint.com/mysql-foreign-keys-quicker-database-development/

[sintax63]

AH! Well I'm not going to know what that is. See the rubric table is static but the schedule table changes every week and gets wiped out and reimported from a CSV file that is generated by another program. Perhaps I'm just out of luck on this one.

[sintax63]

I have been messing with this all weekend and kind of got another way working, but it also has a flaw that I'm not sure can be corrected.

 

$query="SELECT * FROM rubric WHERE title NOT LIKE '%*%' GROUP BY title ASC";
$result=mysql_query($query);
    
while ($list = mysql_fetch_array($result)) { 

	$letter	= strtoupper(substr($list['title'],0,1)); 
	$code 	= $list['code'];
	$title 	= $list['title'];

include("../includes/globals.php");
       
	if ($letter != $prev_row) { 

		echo "<a name=\"$letter\"></a> $letter \n"; 

	}

	echo "<li><a href=\"topic.php?code=$code\">$title</a></li> \n";

	$prev_row = $letter; 

}

 

That gives me a list and layout that I can make work, with one exception. It is listing all the results from rubric, even if nothing in the schedule table has a matching value. Is there a way to only have echo a $title if it finds a matching entry for $code in the schedule table?

[sintax63]

I think I may have totally just gotten it!

 

$query = "SELECT * FROM `rubric` AS r, `schedule_desc` AS s WHERE r.code LIKE CONCAT(SUBSTR(s.name,0,4),'%') AND topic NOT LIKE '%*%' GROUP BY topic ASC";

 

Still listing rubric that have no matching schedule entries.

[xyph]

SELECT r.title, r.code, s.name, s.short_title 
FROM schedule s
INNER JOIN rubric r 
ON substring(r.code,1,3) = substring(s.name,1,3)
ORDER BY r.code

[sintax63]

SELECT r.title, r.code, s.name, s.short_title 
FROM schedule s
INNER JOIN rubric r 
ON substring(r.code,1,3) = substring(s.name,1,3)
ORDER BY r.code

 

THANK YOU. THANK YOU. THANK YOU!

 

How can I buy you a beer or a coffee for all that time (and your patience!)?